summing number of 'y's in a filtered column

N

neowok

Basically I have a spreadsheet which is filtered by column R and I wan
to sum the number of 'Y's in column P based only on the visibl
filtered table rather than hidden rows.

Now I know the subtotal worksheet function will ignore hidden rows
however it does not seem to be able to total up the number of 'Y's i
the range of cells. The count function would sum up the number of 'y'
but it counts the hidden columns as far as I know.

Is there some other function which would allow me to sum the number o
'y's ignoring hidden rows
 
D

Domenic

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(P2:p10,ROW(P2:p10)-MIN(ROW(P2:p10)),0,1))),--(P2:p10="Y"))

Hope this helps!
 

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

Formula for filtered worksheet 1
Count the Y's in a filtered column that contains Y and N 1
Counting the number of unique values in a filtered list 5
Conditional SUBTOTAL in XL210 Table 7
COUNTIF Function on filtered data 2
Summing a variable number of rows 3
Characters in logical calculations 1
Summing based on a range of criteria 3

Top