View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
HierkommtdieMau
 
Posts: n/a
Default Counting items in auto filter - how?


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!


--
HierkommtdieMau
------------------------------------------------------------------------
HierkommtdieMau's Profile: http://www.excelforum.com/member.php...o&userid=32867
View this thread: http://www.excelforum.com/showthread...hreadid=527060