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

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
 
R

Rowan

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count only visible formula results 2
Countif visible 4
Count only visible results of formula 3
COUNTIF between dates and if value = "y" 8
COPY PASTE VALUES ONLY FROM/ON VISIBLE CELLS 6
HELP: Selecting Non-Visible (Hidden) Cells with AutoFilter Applied 3
Count only visible cells 4
Need to combine countIF with AND for selection criteria 1

Top