I would like to have my day schedule to be linked to its day hours so that it produces my total week hours.
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. 
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.
-
1Is there anything you tried yourself? Please edit your question to include this info.– ZygDDec 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?– TedinozDec 13, 2021 at 21:01
Add a comment
|
1 Answer
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
