Counting items in auto filter - how?

I have documents with say 38,000 lines and poss ten cols
With auto filter on I can select a species in col A and then I want to know
how many different locations it was found at which is listed in col B. At
present I hold the auto filter down on col B and actually count down the
list! There must be a better way.
Help please!

Hi,
Not sure I understand exactly what you're trying to do, but does the following article help?

"Can I write a formula that returns the number of distinct (=different) en-tries in a range?
First, let's clarify the question. We're hunting for a formula that, given the range 100, 99, 98, 100, 98, 100, 98, would return 3 because there are three different numbers used. This type of counting requires an array formula. The formula below, for example, counts the number of distinct entries in the range A1:D100.

=SUM(1/COUNTIF(A1:D100, A1:D100))

When you enter this formula, you must press Ctrl-Shift-Enter. Pressing only Enter will give you the wrong result. Excel will place brackets around the formula to remind you that you've created an array formula.

The preceding formula works fine in many cases, but it will return an er-ror if the range contains any blank cells. The formula below (also an ar-ray formula, so input it with Ctrl-Shift-Enter) is more complex, but it will handle a range that contains a blank cell.

=SUM(IF(COUNTIF(A1:D100,A1:D100)=0, "", 1/COUNTIF(A1:D100,A1:D100)))
"

This comes from "http://www.pcworld.com/howto/article/0,aid,15849,pg,1,00.asp", perhaps it helps!

It would help if you insert a new row at the top of your data, then you
will be able to see the count even if you scroll down if you make use
of Window | Freeze Pane. In this top row you can enter a formula like:

=SUBTOTAL(3,B3:B38000)

or whatever range you have - I've assumed that you have a header row,
and this will become row 2 so that your data starts in row 3.

The SUBTOTAL(3,range) is the equivalent of COUNTA(range), but it only
counts the displayed items when a filter is active.

Hope this helps.

Pete

A word of caution about counting items in the autofilter window: If there are many items, sometimes the autofilter list is truncated, so a count may be incorrect. I believe the limit is 1000 unique items.

---GJC

That is just for display in dropdown, have no influence over subtotal
formulas so Pete's answer will still work


--

Regards,

Peo Sjoblom




gjcase" <[email protected]> wrote in
message news:[email protected]...
>
> A word of caution about counting items in the autofilter window: If
> there are many items, sometimes the autofilter list is truncated, so a
> count may be incorrect. I believe the limit is 1000 unique items.
>
> ---GJC
>
>
> --
> gjcase
> ------------------------------------------------------------------------
> gjcase's Profile:
> http://www.excelforum.com/member.php...o&userid=26061
> View this thread: http://www.excelforum.com/showthread...hreadid=527060
>