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!

=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)),,))))

--
Regards,

Peo Sjoblom






wrote in message
ups.com...
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!

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?



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)),,))))


I have several custom views set to view various filtered populations of this group of people (different affiliations - in column C if it matters).

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



wrote in message
ups.com...
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?



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)),,))))


I have several custom views set to view various filtered populations of
this group of people (different affiliations - in column C if it
matters).

Do you use an English version of Excel?

If no, what language do you use?


wrote:

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?

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)),,))))

I have several custom views set to view various filtered populations of this group of people (different affiliations - in column C if it matters).

--

Dave Peterson

Yes, English. Excel 2007.

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

Thanks for looking at this Dave!



On Aug 15, 8:34 pm, Dave Peterson wrote:
Do you use an English version of Excel?

If no, what language do you use?

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.

wrote:

Yes, English. Excel 2007.

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

Thanks for looking at this Dave!

On Aug 15, 8:34 pm, Dave Peterson wrote:
Do you use an English version of Excel?

If no, what language do you use?

Dave Peterson

--

Dave Peterson