I am working on a work breakdown / Gantt chart type of spreadsheet where I need to know the number of hours planned for certain resource types during a certain period. An example spreadsheet might look like this:
I know I can do something like =SUMIF($C$3:$C$8, "Dev", D3:D8)
that will give me the sum for all things in row D3 to D8 when the resource type is "Dev", for example the value in cell D10. Similary =SUMIF($C$3:$C$8, "Tester", F3:F8)
will give me the number 6 in cell F11 and by doing =SUM(D10:H10)
, I will get the number of hours for Dev in cell H13 and similarly for H14 for the Tester Hours sum.
I am trying to eliminate the middle steps for summing first each column and then summing again from the resulting data from D10 and H10, etc.
I know =SUM(D3:H8)
is possible, but this will not allow me to differentiate the Dev Hours vs Tester Hours. I am unable to do something like =SUMIF($C$3:$C$8, "Dev", $D$3:$H$8)
for some reason to get me the 14 hours I am expecting for Dev.
I tried multiple things using ARRAYFORMULA
and SUMIF
and that too didn't work out.
Anyone know if this is even possible without doing the intermediate step?
CodePudding user response:
try:
=INDEX(SUM(IF(B3:B8="dev", C3:G8, )))
or:
=SUMPRODUCT((B3:B8="tester")*C3:G8)