How do I count values w/ criteria located in more than one column

B

Brian

I need to count individuals' scores based on 2 criteria: the Team each
individual belongs to (Team 1, 2, 3 or 4) and the individual's score itself.
So, in a database sorted alphabetically by name (not by Team), I'd want to
know (for example) how many individuals scored 30-35 points in Team 1.

Is there a worksheet function (or combination of functions) that can
accomplish this?

This is an example of how my database is set up. The headers TEAM, NAME &
SCORE represent columns A, B & C.
TEAM NAME SCORE
1 ADDISON 23
2 ANTENOR 19
1 BASZLER 26
4 ABRAHAM 25
3 ANSON 36
3 ARIAS 12
2 BADGLEY 18
4 ADAMS 16
2 ALLEN 16
1 BELL 9
3 BENAVIDES 24
1 AKRE 17
1 BARISON 39
4 BARNER 36
3 BARTON 23

Thanks,
Brian
 
T

T. Valko

how many individuals scored 30-35 points in Team 1.

=SUMPRODUCT(--(A2:A16=1),--(C2:C16>=30),--(C2:C16<=35))

Based on your sample data, the answer is 0.

Better to use cells to hold the criteria:

E1 = team
E2 = lower point boundary
E3 = upper point boundary

=SUMPRODUCT(--(A2:A16=E1),--(C2:C16>=E2),--(C2:C16<=E3))

Biff
 
M

Martin Fishlock

Brian you need to use the following:

=SUMPRODUCT(--(A2:A16=1),--(C2:C16>=30),--(C2:C16<=35))

The criteria can be put is cells so you can say A2:A16=E10...

The other way to do it is to use auto filter with custom filters and the
subtotal

=SUBTOTAL(2,C2:C16) at the bottom with one line free.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top