Counts/Percents Multiple Criteria

[Michael]

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

 

[Bob Phillips]

=SUMPRODUCT(--($A$2:$A$20=1),--($C$2:$C$20="Decrease"),--($B$2:$B$20>12))

=SUMPRODUCT(--($A$2:$A$20=1),--($C$2:$C$20="Decrease"),--($B$2:$B$20>8),--($
B$2:$B$20<=11))

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bernard Liengme]

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