countif only visible cells (filtered)

[donnap99mail]

I have a few columns where I need to count the number of "Y", "N",
"?", and blank. At the top of my spreadsheet I added 4 rows, and at
the top of each of the columns, I have the following (see below). This
is counting ALL cells. I have several custom views set to view various
filtered populations of this group of people (different affiliations -
in column C if it matters). I need to see only the number for the
visible cells. I've been fooling around with the SUBTOTAL function,
but I can't get it to work.

=COUNTIF(I5:I200,"Y")
=COUNTIF(I5:I200,"N")
=COUNTIF(I5:I200,"?")
=COUNTIF(I5:I200,"")

Thanks!

 

[Peo Sjoblom]

=SUBTOTAL(3,I5:I200)

then filter for Y, then filter for N and so on

If somehow you are filtering another column and you want count the Y you can
use this technique


=SUMPRODUCT(--($I$2:$I$200="Y"),--(SUBTOTAL(3,OFFSET($I$2,ROW($I$2:$I$200)-MIN(ROW($I$2:$I$200)),,))))

 

[donnap99mail]

I am filtering on criteria in column C. The counts are coming from
column I.

I cut and pasted your formula below, but it's not working - the result
is #NAME?

 

[Peo Sjoblom]

You must have pasted it incorrectly or gotten excessive characters or too
few

=SUMPRODUCT(--($I$2:$I$200="Y"),--(SUBTOTAL(3,OFFSET($I$2,ROW($I$2:$I$200)-MIN(ROW($I$2:$I$200)),,))))



If you have to, do type in the formula. It works, I promise.

You do want to count Y ? If you want to just count visible cells just use

=SUBTOTAL(3,I2:I200)

my formula will count Y in the filtered range (or unfiltered for that
matter)



--
Regards,

Peo Sjoblom



I am filtering on criteria in column C. The counts are coming from
column I.

I cut and pasted your formula below, but it's not working - the result
is #NAME?

 

[Dave Peterson]

Do you use an English version of Excel?

If no, what language do you use?

 

[donnap99mail]

Yes, English. Excel 2007.

I tried the =SUMPRODUCT..... again. Still not working.

Thanks for looking at this Dave!

 

[Dave Peterson]

You could try copying a copy of Peo's formula again (just in case google
introduced some funny characters).

=SUMPRODUCT(--($I$2:$I$200="Y"),
--(SUBTOTAL(3,OFFSET($I$2,ROW($I$2:$I$200)-MIN(ROW($I$2:$I$200)),,))))

(if that still fails, try typing it in manually.)

And if that fails, copy the formula from the formula bar and post it in your
reply.

 

[Peo Sjoblom]

They actually do add some characters, sometimes I search for my own old
formulas and when I copy and paste them on some occasions there have been
things like an extra minus sign etc



--
Regards,

Peo Sjoblom

 

[Dave Peterson]

And sometimes those characters are invisible to the naked eye--just more HTML
junk <vbg>.