Alternative to adding multiple SUMIFS?

[DoktorPhill]

This is an hourly results table which can be extended quite far to the right, I want to add the values which correspond to all instances of each code (A,B,C etc), so in the table below "A" would be 1050 (100+100+200+250+100+300).
The codes are listed in a table elsewhere and the total will be next to them.

Currently I'm achieving this by using SUMIF($E$7:$E$16, $B41,$C$7:$C$16)+SUMIF($H$7:$H$16, $B41,$F$7:F$16)+SUMIF.... 24 times.. (B41 is the cell containing "A") but that seems inefficient

Is there a way to say, combine the whole table into one range and find the values at RC[-2] of each "A" and sum them?

 

[jasonb75]

You could use

=SUMIF($E$7:$Z$16, $B41,$C$7:$X$16)

This method will not always work if there is the possibility of other intermediate columns containing numeric values and entries that meet the criteria but with your layout there would be no problems.

 

[RoryA]

You could do something like:

=SUMPRODUCT(($E$6:$Z$6="G")*($E$7:$Z$16="A")*($C$6:$X$6="C"),($C$7:$X$16))

note the the second pair of ranges are two columns to the left of the first, but the same size. This should allow for the possibility of any A values appearing in other columns.

 

[DoktorPhill]

You could use

=SUMIF($E$7:$Z$16, $B41,$C$7:$X$16)

This method will not always work if there is the possibility of other intermediate columns containing numeric values and entries that meet the criteria but with your layout there would be no problems.

Thanks Jason, I knew it would be simple, but not THAT simple. Works perfectly.

 

[jasonb75]

You're welcome!

To follow up on my comments that the method might not always work, it will sum any numbers which are 2 cells to the left (based on the current ranges) of a cell containing the criteria.
With your layout this would mean that if you had "A" in one of the HC columns with a number in the same row of the previous G column then it would be included in the total. Looking at your example I wouldn't expect that to happen but if it is possible then the method that @RoryA suggested would prevent the error.

 

[DoktorPhill]

It still works with numbers in the HC columns as intended, and it is all Data Validated so letters cant be in the C or HC columns. Thanks again.