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

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

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

One way

=SUMPRODUCT(--(A2:A50=1),--(C2:C50=30),--(C2:C50<=35))


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)




"Brian" wrote in message
...
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

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

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

"Brian" wrote in message
...
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

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

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Brian" wrote:

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