Using AUTOFILTER with FREQUENCY - ?

Hi All,

I have the following data and have created the FREQUENCY distribution below it.

Ideally, what I want is to be able to see the frequency distribution and use AUTOFILTER on the dates to see the FREQUENCY distribution is for various dates or combinations of dates.

However, the frequency distribution doesn't seem to get updated when the autofilter is applied.

Ideas and comments welcome!

Please Login or Register  to view this content.

Assuming your dates are in A2:A11 and categories in B2:B11, then with a specific category in D1 this formula in E1 will count the visible matching categories only

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B$2:B$11)-ROW(B$2),0)),--(B$2:B$11=D1))

drag formula down for counts of categories in D2, D3 etc.....

Thanks Kieran, I had considered that, unfortunately there are several columns (apart from date) I want to filter on in the real example...

Thanks for your reply ddll!
I'm still trying to get my brain around your solution!
(I've never used OFFSET or SUMPRODUCT before!)

It doesn't quite work right but i'll debug it.
What does the "- -" mean at the end of your formula?
--(B$2:B$11=D1))

Originally Posted by zerocred

It doesn't quite work right but i'll debug it.
What does the "- -" mean at the end of your formula?
--(B$2:B$11=D1))

(B$2:B$11=D1) gives you an array of TRUE/FALSE values. These need to be converted to 1/0 values which is what the -- does. You could also do that several other ways e.g.

(B$2:B$11=D1)+0

Note: for "copying down" purposes I should have included an extra $ in the first B2, i.e.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B$2,ROW(B$2:B$11)-ROW(B$2),0)),--(B$2:B$11=D1))