countif for only visible rows when combined with autofilter - possible?
 

I would like to use countif or something that works like that on a
autofiltered column to count occurences of several values, like a
frequency list.

The problem is that not just the visible cells are counted but the
hidden ones as well.
Is there a way to do this, subtotal can count all visible rows in a
column but I need to separate the different values.

/Johan


--
johli
------------------------------------------------------------------------
johli's Profile: http://www.excelforum.com/member.php...o&userid=27388
View this thread: http://www.excelforum.com/showthread...hreadid=469427

You could use a sumproduct function, this example counts all the visible
records in column A which have the value "Rowan":

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(A2:A100,1,1),ROW(A2:A100)-ROW(INDEX(A2:A100,1,1)),0))=1),--(A2:A100="Rowan"))

Hope this helps
Rowan

johli wrote:
I would like to use countif or something that works like that on a
autofiltered column to count occurences of several values, like a
frequency list.

The problem is that not just the visible cells are counted but the
hidden ones as well.
Is there a way to do this, subtotal can count all visible rows in a
column but I need to separate the different values.

/Johan