filter

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
K

Khalil Handal

Sum gives the total of all numerical values even when autofilter is on
Subtotal gives the correct sum for the filtered rows
How can I have a list of all functions that works with autofilter is on.
 
Only subtotal, if you need to do countif etc on filtered lists you can use
the method here


=SUMPRODUCT(--($K$2:$K$560=1),(SUBTOTAL(3,OFFSET($K$2,ROW($K$2:$K$560)-MIN(ROW($K$2:$K$560)),,))))


will count the visible cells where K2:K560 equals 1



--


Regards,


Peo Sjoblom
 
See help on Subtotal for a list.


1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP


Gord Dibben MS Excel MVP
 
thanks to both of you.

Gord Dibben said:
See help on Subtotal for a list.


1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top