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" 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
...
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" 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" 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