You could add a "count" column using =SUBTOTAL(3,A2) filled down from row 2,
this returns 1 if the row is visible and 0 if it is filtered.
Now you can fill down in the "freqüency" column use something like:
=SUMIF(category,cat,count)
"zerocred" wrote:
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!
Code:
date category
01/01/2008 1
01/01/2008 1
01/01/2008 2
01/01/2008 4
02/02/2008 1
02/02/2008 2
02/02/2008 3
02/02/2008 3
03/03/2008 2
03/03/2008 4
Freq distribution:
cat frequency
1 3
2 3
3 2
4 2
Ideal output when filtered on the a single date
Filtered for date: 01/01/2008
cat frequnecy
1 2
2 1
3 0
4 1
Excel 2003