indexing - Calculate total working hours per month per task in Excel

Question

I have set up an excel sheet for calculating one person's hours per task per month. So far I have set up 3 cascading dropdowns: Work Package, Activity and Deliverable. These are followed by a cell for the number of hours worked on that task. There are three sets of dropdowns per day.

What I still need is to sum the hours per work package, per activity and per deliverable. I have tried Sum, index and match:

=SUM(INDEX(E5:E9,0,MATCH(B14,B5:B10,0)))

However it counts all of the hours. I assume because it only needs to be mentioned once in that range to meet the criteria. Is there a way to do it without having to do the formula for each set. Is there one formula I can use for each day or all 30 days in September 2020. I'd also like to incorporate dropdowns for getting the sums (see A14:A17)

question from:https://stackoverflow.com/questions/65847781/calculate-total-working-hours-per-month-per-task-in-excel

Answer 1

Waitting for answers