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)

"Michael" <[email protected]> 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
>
>

[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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Michael" <[email protected]> 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
>
>