Total multiple columns for multiple teams & groups

Question

I need to total the teams A B C D for Girls & Boys However the order of the teams changes each week, so I can not do a simple:

GIRLS TEAM A = B3+G3
GIRLS TEAM B = C3+H3

    A       B   C   D   E   F   G   H   I
1           Saturday        Sunday          
2           A   B   C   D   A   B   C   D
3   Girls   3   4   4   2   8   3   6   2
4   Boys    2   4   6   3   6   4   6   5

I can not use VLOOKUP; will not work due to the team names being vertical. I have also tried Using Named Ranges & Intersection (SPACE) Operator however this doesn't work with each team name appearing twice.

Can you offer any suggestions? The lay out can not be amended.

Answer 1

You can use SUMPRODUCT to get the total for Girls in Team A, e.g. using your example

=SUMPRODUCT((B2:I2="A")*(A3:A4="Girls"),B3:I4)

If you always know that girls will be row 3 then SUMIF would be sufficient, i.e.

=SUMIF(B2:I2,"A",B3:I3)