How to sum all values in a region but only if the row is classified to be of a certain type in Google Spreadsheet?

Question

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?

Answer 1

try:

=INDEX(SUM(IF(B3:B8="dev", C3:G8, )))

or:

=SUMPRODUCT((B3:B8="tester")*C3:G8)