Add and count after filtering

**Quagga** · 01-24-2012, 12:46 PM

I have set formula to add and count rows and have got that part right.
Once I use the filter, the totals remain the same.
How do I change these formula to recognise the filtering I have selected.
I want to see the new totals for the rows that are displayed.

I have attached a simple example to show what I want to achieve.

Is this possible?

Thanks for your time.

**NBVC** · 01-24-2012, 12:58 PM

Try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$12:$B$100,ROW($B$12:$B$100)-MIN(ROW($B$12:$B$100)),,1)),--($I$12:$I$100>0),--($J$12:$J$100=D3))-SUMPRODUCT(SUBTOTAL(3,OFFSET($B$12:$B$100,ROW($B$12:$B$100)-MIN(ROW($B$12:$B$100)),,1)),--($I$12:$I$100<0),--($J$12:$J$100=D3))

copied down

**Quagga** · 01-24-2012, 01:28 PM

Thanks for the help with the SUMPRODUCT formula......what would the COUNTIFS formula look?

**NBVC** · 01-24-2012, 02:04 PM

There isn't really a COUNTIFS equivalent because the SUBTOTAL(3,OFFSET($B$12:$B$100,ROW($B$12:$B$100)-MIN(ROW($B$12:$B$100)),,1)), which does the filtered calcs is not allowed.

**Quagga** · 01-24-2012, 02:22 PM

I have noticed that the SUMPRODUCT formula is actually counting the number of entries and not adding the amounts up to give me a sum for that selection. How do I do this?