Apply filter then subtotal

This link may help.

http://www.ozgrid.com/Excel/excel-subtotal-function.htm

Try this formula in C4 for the most frequent product

=INDEX(B7:B140,MODE(IF(SUBTOTAL(3,OFFSET(B7,ROW(B7:B140)-ROW(B7),,)),MATCH(B7:B140,B7:B140,0))))

This is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

Now in D4 for the number.....

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B7,ROW(B7:B140)-ROW(B7),,)),--(B7:B140=C4))

Hi,

Thanks for the quick replies.

ncmay - I will check out that site a little futher, thanks.

daddylonglegs - both formulas work perfect, thanks for that. I was wondering I could ask for your help once more. I am curious weather there would be a way to perhaps show the volume of entries per account per period, sort of like a trend. Eg, F1:K1 would read P01, P02, P03 etc, with the count of each beneath this, based on the autofilter selection of account, so therefore the count would update similar to the formulas you produced before. So, if the user were to select the sheet would look like:

P01 P02 P03 P04....
3 3 0 1

Again, I tried to have a bit of play around however fell at the first hurdle!

Any help would be really appreciated
Thanks again

You can use a formula similar to the SUMPRODUCT formula I used above, i.e. in F2 copied across

=SUMPRODUCT(SUBTOTAL(3,OFFSET($E7,ROW($E7:$E140)-ROW($E7),,)),--(RIGHT($E7:$E140,3)=F1))

Thanks for that mate, that works great!