Counting in a AutoFilter column

[Kicker]

I have a worksheet with about 40,000 records and use AutoFilter to limit the number of records displayed based on 2 different columns. For example, a person's name and a date will limit the displayed entries to about 8-15 lines.

I need to count how many times a specific "item" is found in the list. Somedays it will be zero and others it might be 8 or more. If I use

=countif(range, criteria)

It counts through the entire 40,000+ records. Is there a way to limit the function to the filtered records? I KNOW my result should be 2 but it is counting 1,245 from the entire database.

[smc2911]

Try SUBTOTAL(2,range). The subtotal function will restrict its calculation to Auto-Filtered results. The parameter '2' selects COUNT (as opposed to SUM, e tc.)There is not a countif version, but it should not be necessary as you can just filter for your criterion.

Here is a list of the possible SUBTOTAL types (from help). Using the second column as your

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

Note that these will include hidden cells. To exclude hidden cells use 101, 102, 103 etc.

Sean.

[Kicker]

Wow...I can't wait to get back to work tomorrow to try this out.

Yeah, right..LOL :beerchug:

Thanks. I don't think I would have ever tried subtotal. Appreciate the help.

[Kicker]

That didn't work. Let me try it again.

An example of the column in question would be...

Item one
Item two
Item three
Item one
Item two
Item three
Item one
Item two
Item three
Item two
Item two
Item two
Item two
Item two
Item two

There are more "Item two"s than anything else. The list above IS the filtered list. I need to know how many "Item two"s are in the list. Actually, I need to count how many of each item in the filter list.