How to sum hours based on criteria in to "tables"

How many hours for P4 ?

In this case: None :-) The first liste shows the relation between Projects and Categories. The second list shows hours spent on each project. In some periodes no one has worked on a specific project.

Try this
I2:
=SUMPRODUCT(($E$2:$E$4=$A$3:$A$5)*(B3:B5="x")*$F$2:$F$4)
I3:
=SUMPRODUCT(($E$2:$E$4=$A$3:$A$5)*(C3:C5="x")*$F$2:$F$4)

Originally Posted by Phuocam

Try this
I2:
=SUMPRODUCT(($E$2:$E$4=$A$3:$A$5)*(B3:B5="x")*$F$2:$F$4)
I3:
=SUMPRODUCT(($E$2:$E$4=$A$3:$A$5)*(C3:C5="x")*$F$2:$F$4)

Thank you very much for your solution. It Works. But i in your solution the (B3:B5="x") is hardcoded. The relation between the categories is hardcoded. I can't be sure, that category "C1" will be listed in column B. It might be in column B or C or D. Is it possible to build a more generic version of the formula ? :-)

Is this generic enough?

=SUMPRODUCT(COUNTIFS($A$3:$A$6,$E$2:$E$4,INDEX($B$3:$C$6,0,MATCH($H2,$B$2:$C$2,0)),"x"),$F$2:$F$4)

Originally Posted by jason.b75

Is this generic enough?

=SUMPRODUCT(COUNTIFS($A$3:$A$6,$E$2:$E$4,INDEX($B$3:$C$6,0,MATCH($H2,$B$2:$C$2,0)),"x"),$F$2:$F$4)

Boy, you're skilled :-) I'll try it out right away :-)

Originally Posted by jason.b75

Is this generic enough?

=SUMPRODUCT(COUNTIFS($A$3:$A$6,$E$2:$E$4,INDEX($B$3:$C$6,0,MATCH($H2,$B$2:$C$2,0)),"x"),$F$2:$F$4)

Had to translate it to Danish. The good news: It Works perfectly. Thank you very much! :-) Have a nice weekend.