Determine Frequency in Filtered List

Hi Folks - I've seen similar requests, but not exactly what I was looking
for. I have a list:

Class Instructor Rating
Excel Smith 5
Access Jones 4
Excel Brown 3

etc.....


I can use the Frequency function to determine how many 3, 4 and 5 ratings I
get for the entire list. I'd like to be able to filter the list by class and
instructor, and the get the frequency data for the filtered list. Any ideas?

Thanks.

Michael

Michael,

Use a Pivot Table. Drag all three buttons to the row area (Rating first)
and then drag either cloas or instructor to the data area (since they are
strings, it will default to count rather than sum). Then you can show
whichever combination of values you want.

HTH,
Bernie
MS Excel MVP

"Michael" <[email protected]> wrote in message
news:8zLOd.42933$B95.11631@lakeread02...
> Hi Folks - I've seen similar requests, but not exactly what I was looking
> for. I have a list:
>
> Class Instructor Rating
> Excel Smith 5
> Access Jones 4
> Excel Brown 3
>
> etc.....
>
>
> I can use the Frequency function to determine how many 3, 4 and 5 ratings
I
> get for the entire list. I'd like to be able to filter the list by class
and
> instructor, and the get the frequency data for the filtered list. Any
ideas?
>
> Thanks.
>
> Michael
>
>
>

Bernie - Client is not Pivot Table savvy. I need to provide all stats
without user intervention ....Ideas? I'm not married to using the frequency
function, so if there is a way to produce the same result, I'm all ears
......



Michael


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> Michael,
>
> Use a Pivot Table. Drag all three buttons to the row area (Rating first)
> and then drag either cloas or instructor to the data area (since they are
> strings, it will default to count rather than sum). Then you can show
> whichever combination of values you want.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Michael" <[email protected]> wrote in message
> news:8zLOd.42933$B95.11631@lakeread02...
>> Hi Folks - I've seen similar requests, but not exactly what I was looking
>> for. I have a list:
>>
>> Class Instructor Rating
>> Excel Smith 5
>> Access Jones 4
>> Excel Brown 3
>>
>> etc.....
>>
>>
>> I can use the Frequency function to determine how many 3, 4 and 5 ratings
> I
>> get for the entire list. I'd like to be able to filter the list by class
> and
>> instructor, and the get the frequency data for the filtered list. Any
> ideas?
>>
>> Thanks.
>>
>> Michael
>>
>>
>>
>
>

Try the following...

=SUM(IF(FREQUENCY(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-MIN(ROW(A2:A10)),0
,1))*C2:C10,SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-MIN(ROW(A2:A10)),0,1))*C
2:C10)>0,1))-(SUBTOTAL(3,A2:A10)<COUNTA(A2:A10))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <8zLOd.42933$B95.11631@lakeread02>,
"Michael" <[email protected]> wrote:

> Hi Folks - I've seen similar requests, but not exactly what I was looking
> for. I have a list:
>
> Class Instructor Rating
> Excel Smith 5
> Access Jones 4
> Excel Brown 3
>
> etc.....
>
>
> I can use the Frequency function to determine how many 3, 4 and 5 ratings I
> get for the entire list. I'd like to be able to filter the list by class and
> instructor, and the get the frequency data for the filtered list. Any ideas?
>
> Thanks.
>
> Michael