Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default countif only visible cells (filtered)

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!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default countif only visible cells (filtered)

=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!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default countif only visible cells (filtered)

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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default countif only visible cells (filtered)

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default countif only visible cells (filtered)

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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default countif only visible cells (filtered)

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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default countif only visible cells (filtered)

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" wrote in message
...
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



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default countif only visible cells (filtered)

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

Peo Sjoblom wrote:

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" wrote in message
...
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


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy visible cells on Filtered data [email protected] Excel Discussion (Misc queries) 4 July 18th 07 08:18 AM
count if on Visible - Filtered tonyv Excel Worksheet Functions 4 July 7th 07 07:34 PM
How do I only delete/clear the visible cells in a filtered list? Merv Excel Worksheet Functions 5 March 2nd 07 08:00 PM
Help to adapt Formula syntax to work with Visible Filtered Cells Sam via OfficeKB.com Excel Worksheet Functions 6 September 16th 05 11:42 PM
average of visible cells in a filtered range dave roth Excel Worksheet Functions 5 May 23rd 05 12:56 PM


All times are GMT +1. The time now is 09:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"