Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count in column A when it meets all criteria in three col | Excel Worksheet Functions | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |