School Athletics Sheet - Sum of House Points

[111333222111]

Hi All,

I'm looking for a formula to add house points together. The houses (Blue, Gold, Green, Red) will needed to be added separately and shown in B47, C47, D47, E47.

The house points total will come from adding the numbers in cells E5:41+G5:G41+I5:I41+K5:K41+M5:M41+O5:O41+Q5:Q41

So basically I don't know how to write a formula that only adds the scores from the students in 'BLUE' house and shows the total in B47. The same with all three other houses.

Any help is appreciated!

Thank you!

[Pete_UK]

You can put this formula in cell B47:

=SUMPRODUCT(($D$5:$D$41=B46)*(($E$4:$Q$4="Cone")+($E$4:$Q$4="Pts")),$E$5:$Q$41)

then copy across into C47:E47.

Hope this helps.

Pete

[111333222111]

You can put this formula in cell B47:

=SUMPRODUCT(($D$5:$D$41=B46)*(($E$4:$Q$4="Cone")+($E$4:$Q$4="Pts")),$E$5:$Q$41)

then copy across into C47:E47.

Hope this helps.

Pete

Pete, thank you so much! That worked perfectly.

Just one more, if you or anyone else has time.

In the 'M' column I have a formula, which does work but I was wondering if there was a cleaner way of setting it out.

=IF(ISBLANK(N5),"",IF(AND(N5<=15,N5>1),4,IF(AND(N5>15,N5<=18),3,IF(AND(N5>18,N5<=20),2,IF(N5>=20.01,1)))))

N5 = blank, M5 = blank
N5 = bigger than 1 and smaller than or equal to 15, M5 = 4
N5 = bigger than 15 and smaller than 18, M5 = 3
N5 = bigger than 18 and smaller than 20, M5 = 2
N5 = bigger than 20.01, M5 = 1

Thanks!

[AliGW]

Try this:

=IF(ISBLANK(N5),"",LOOKUP(N5,{2,16,19,20.011},{,{4,3,2,1})

[avk]

You can also use :
In "M2"

Please Login or Register  to view this content.

copy down
In "B2"

Please Login or Register  to view this content.

copy down
In "B47"

Please Login or Register  to view this content.

copy paste across
Plz refer attach file.

[JohnTopley]

In M

=IF(ISBLANK(N5),"",IF(N5<=15,4,IF(N5<=18,3,IF(N5<=20,2,1))))