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

  • Thread starter Thread starter johli
  • Start date Start date
J

johli

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

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

/Joha
 
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
 
Back
Top