Counting the Range showing when the filters are on

Posted by Paul Magruder on February 26, 2001 4:34 AM

Is there a way to count only the values showing, after the filters are on. I am using a macro to turn the filters on, and the range of the data changes with each data entry. The problem is if I use the count function ( and the range is set for the entire column),Excel counts all the data in the column,instead of only the filtered data. I also need to "Sum" in the same way.

Thanks in advance
Paul Magruder

Posted by David Hawley on February 26, 2001 4:46 AM

Hi Paul

You are looking for the SUBTOTAL function. To count visible cells that have numbers use:

=SUBTOTAL(2,A1:A14)

The text below is from the Excel help

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Ref1, ref2, are 1 to 29 ranges or references for which you want the subtotal.

Remarks

If there are other subtotals within ref1, ref2, (or nested subtotals), these nested subtotals are ignored to avoid double counting.

SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered.

If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.

Example

SUBTOTAL(9,C3:C5) will generate a subtotal of the cells C3:C5 using the SUM function

Dave

OzGrid Business Applications

Posted by Paul Mgruder on February 26, 2001 9:52 AM

Thanks very much for your help.It just what I needed

Paul