Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counts/Percents Multiple Criteria
Hi Folks - Me again ... Here's my scenario:
I have data arranged like this: SiteID Attendance Count ComparisonField 1 20 Increase 1 10 Decrease 1 5 Same 1 25 Decrease 2 20 Increase 2 10 Decrease 2 5 Same 2 25 Decrease I need a way to produce stats like this: SiteName Attendance Groupings Comparison Counts # of increase # of Decreases # of Same 1 12 or more 1 1 0 Between 8-11 0 1 0 Less than 8 0 0 1 So, for any given site, I'd like to count the number of increases, decreases and sames for the attendance groupings. I tried a Pivot Table, bu could not get the correct results. I'm think an array formula may do the trick. Any ideas? Thanks. Michael |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counts/Percents Multiple Criteria
=SUMPRODUCT(--($A$2:$A$20=1),--($C$2:$C$20="Decrease"),--($B$2:$B$2012))
=SUMPRODUCT(--($A$2:$A$20=1),--($C$2:$C$20="Decrease"),--($B$2:$B$208),--($ B$2:$B$20<=11)) etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Michael" wrote in message news:pWPDf.148428$WH.75374@dukeread01... Hi Folks - Me again ... Here's my scenario: I have data arranged like this: SiteID Attendance Count ComparisonField 1 20 Increase 1 10 Decrease 1 5 Same 1 25 Decrease 2 20 Increase 2 10 Decrease 2 5 Same 2 25 Decrease I need a way to produce stats like this: SiteName Attendance Groupings Comparison Counts # of increase # of Decreases # of Same 1 12 or more 1 1 0 Between 8-11 0 1 0 Less than 8 0 0 1 So, for any given site, I'd like to count the number of increases, decreases and sames for the attendance groupings. I tried a Pivot Table, bu could not get the correct results. I'm think an array formula may do the trick. Any ideas? Thanks. Michael |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counts/Percents Multiple Criteria
Assuming the Site ID to be in A2:A100, the Attendance Count in B2:B100 and
the text in C2:C100 To count Site ID =1; Count =12 and text Increase =SUMPRODUCT(--(A2:A100=1), --(B2:B100=12), --(C2:C100 ="Increase") Of course you could use =SUMPRODUCT(--(A2:A100=K5), --(B2:B100=L5), --(C2:C100 =L6) if K5, L5 and L6 have values 1,12 and Increase, respectively To count Site ID =1; Count = 8 to 11 and text Increase =SUMPRODUCT(--(A2:A100=1), --(B2:B100=8), --(B2:B100<12), --(C2:C100 ="Increase") More details of why this works at http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Michael" wrote in message news:pWPDf.148428$WH.75374@dukeread01... Hi Folks - Me again ... Here's my scenario: I have data arranged like this: SiteID Attendance Count ComparisonField 1 20 Increase 1 10 Decrease 1 5 Same 1 25 Decrease 2 20 Increase 2 10 Decrease 2 5 Same 2 25 Decrease I need a way to produce stats like this: SiteName Attendance Groupings Comparison Counts # of increase # of Decreases # of Same 1 12 or more 1 1 0 Between 8-11 0 1 0 Less than 8 0 0 1 So, for any given site, I'd like to count the number of increases, decreases and sames for the attendance groupings. I tried a Pivot Table, bu could not get the correct results. I'm think an array formula may do the trick. Any ideas? Thanks. Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria | Excel Worksheet Functions | |||
Counting by multiple criteria | Excel Worksheet Functions | |||
COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions | |||
Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) |