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! |
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! |
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). |
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). |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 09:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com