Totalling Data that cannot be sorted numerically

[oliverhj]

Hi everyone. I'm a bit stuck with this one and tried to use a nested If function but can't quite work out how to make it work in this instance, so I'm not sure if that's the right function to be using here.

I have a range of data that is a "score" and each "score" is at a certain "level". The data will be entered randomly, so not necessarily in numerical order and cannot be rearranged (around 50 people will be adding to multiple sheets, so it's not feasible to rearrange it all).

What I would like, would be to total all the values that are for level 1, level 2, level 3 etc into one cell.

I've attached a quick version of what I mean which should explain it. I have made the "levels" a drop down as that's what they will be on the final sheet. I've included the "desired results" on there too so you can see what I mean if I haven't made sense. Unfortunately I put those in manually though so looking for a formula to return those results if possible please!

Any help on this would be much appreciated. :-)

[Special-K]

in E3
=SUMPRODUCT((RIGHT(D3,1)+0=A$3:A$8)*(B$3:B$8))
and copy down

[oliverhj]

Amazing, Special-K. Thank you. Just going to try to transfer that to my original sheet now and make it work. Thanks again for a really fast reply

[JohnTopley]

Change level column to 1, 2, 3 etc i.e. remove text "Level"

=SUMIF(A:A,D3,B:B)

copy down

[oliverhj]

Sorry, it doesn't look like the sample data I provided is actually accurate enough to transfer to my real data.

The attached should hopefully fix this.

Apologies and thanks again!

[Special-K]

Mm

try changing the formula in column H to

=SUMPRODUCT((G3=C$3:C$12)*(D$3:D$12))
and copy down

[JohnTopley]

=sumif($c:$c,g3,$d:$d)