0

I would like to have my day schedule to be linked to its day hours so that it produces my total week hours.enter image description here For example: Schedule is selected from drop down list which is connected by data validation from 'Support Sheet', column "Schedules". Next to each Schedule is its Hours. enter image description here I would like its Hours to correspond to its Schedule so that when it is selected from drop down list on 'database sheet' it will add all the day Schedule Hours to be populated automatically into "week hours" column. I have tried to use Function Sumif= but it only allows me to put one drop down cell in the function as criteria. I tried Sumifs= which uses multiple criteria but won't work, just sums to 0.

2
  • 1
    Is there anything you tried yourself? Please edit your question to include this info.
    – ZygD
    Dec 13, 2021 at 4:31
  • Sumifs WILL work, but it will only produce a result of “8”. It’s because you are forced to add values in the Support sheet rather than the Data sheet. Thought the values in the Data sheet appear identical, they are in reality seven unique values. I would suggest a nested query &/or seven Vlookup formula to get the total. Have you tried anything like that?
    – Tedinoz
    Dec 13, 2021 at 21:01

1 Answer 1

0

I am sure that there are several ways to answer this question.

This answer offers two options, but it is possible that there are other methods and/or more efficient methods.

Option#1 - Nested Queries

=query({query({$K$12:$L$13},"select Col2 where Col1 !='' and Col1 contains '"&A2&"'"),query({$K$12:$L$13},"select Col2 where Col1 !='' and Col1 contains '"&B2&"'"),query({$K$12:$L$13},"select Col2 where Col1 !='' and Col1 contains '"&C2&"'"),query({$K$12:$L$13},"select Col2 where Col1 !='' and Col1 contains '"&D2&"'"),query({$K$12:$L$13},"select Col2 where Col1 !='' and Col1 contains '"&E2&"'"),query({$K$12:$L$13},"select Col2 where Col1 !='' and Col1 contains '"&F2&"'"),query({$K$12:$L$13},"select Col2 where Col1 !='' and Col1 contains '"&G2&"'")},"select Col1+Col2+Col3+Col4+Col5+Col6+Col7 label Col1+Col2+Col3+Col4+Col5+Col6+Col7 ''")

  • copy this down as many rows as there is data

Option#2: Vlookup Query

=query({query({ARRAYFORMULA({VLOOKUP(INDEX( QUERY({flatten(A2:G2)},"select Col1, count(Col1) where Col1 != '' group by Col1 order by count(Col1) desc label Col1 '', count(Col1) ''",0),,1), $K$3:$L$4, 2, 0), QUERY({transpose(A2:G2)}, "select count(Col1) group by Col1 order by count(Col1) desc LABEL count(Col1)''")})},"select Col1*Col2 label Col1*Col2 ''")},"select Sum(Col1) label Sum(Col1) ''")

  • copy this down as many rows as there is data

Sample totals


support

1
  • Thank you very much! I used Option#2 which worked just like i needed.
    – JDL
    Dec 14, 2021 at 4:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.