Determine Frequency in Filtered List

[Michael]

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 Deitrick]

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]

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

 

[Domenic]

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!