If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Countif excluding hidden rows
I would like to count the total number of "1"s and "2"s across a range of
(b8:cu30000). I have an autofilter set up and I would only like to count the exposed rows. I would like to exclude the rows hidden by the autofilter. When I use the countif function, it includes the hidden rows. Any help would be appreciated. Thanks Drew |
#2
|
|||
|
|||
Countif excluding hidden rows
If you filter and only want to count visible rows you can use
=SUBTOTAL(3,A2:A500) If you want some extra criteria for the visible cells =SUMPRODUCT(--($A$2:$A$500=1),(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$ K$500)-MIN(ROW($A$2:$$500)),,)))) for 1 =SUMPRODUCT(--(($A$2:$A$500=1)+($A$2:$A$500=2)0),(SUBTOTAL(3,OF FSET($K$2,ROW($A$2:$A$500)-MIN(ROW($A$2:$A$500)),,)))) for 1 or 2 -- Regards, Peo Sjoblom "qh8519a" wrote in message ... I would like to count the total number of "1"s and "2"s across a range of (b8:cu30000). I have an autofilter set up and I would only like to count the exposed rows. I would like to exclude the rows hidden by the autofilter. When I use the countif function, it includes the hidden rows. Any help would be appreciated. Thanks Drew |
#3
|
|||
|
|||
Countif excluding hidden rows
For that big of a range you should add a column that counts those values by
row. Enter this formula in CV8 and copy down to CV30000: =SUM(COUNTIF(B8:CU8,{1,2})) Then: =SUBTOTAL(9,CV8:CV30000) -- Biff Microsoft Excel MVP "qh8519a" wrote in message ... I would like to count the total number of "1"s and "2"s across a range of (b8:cu30000). I have an autofilter set up and I would only like to count the exposed rows. I would like to exclude the rows hidden by the autofilter. When I use the countif function, it includes the hidden rows. Any help would be appreciated. Thanks Drew |
#4
|
|||
|
|||
Countif excluding hidden rows
Ooops!
I see you wanted a COUNT not a sum. Change the formula in CV8 to: =--(SUM(COUNTIF(B8:CU8,{1,2}))0) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... For that big of a range you should add a column that counts those values by row. Enter this formula in CV8 and copy down to CV30000: =SUM(COUNTIF(B8:CU8,{1,2})) Then: =SUBTOTAL(9,CV8:CV30000) -- Biff Microsoft Excel MVP "qh8519a" wrote in message ... I would like to count the total number of "1"s and "2"s across a range of (b8:cu30000). I have an autofilter set up and I would only like to count the exposed rows. I would like to exclude the rows hidden by the autofilter. When I use the countif function, it includes the hidden rows. Any help would be appreciated. Thanks Drew |
#5
|
|||
|
|||
Countif excluding hidden rows
Well DUH!
LOL! I'll get it right one of these times (I hope!) Disregard my previous reply. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ooops! I see you wanted a COUNT not a sum. Change the formula in CV8 to: =--(SUM(COUNTIF(B8:CU8,{1,2}))0) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... For that big of a range you should add a column that counts those values by row. Enter this formula in CV8 and copy down to CV30000: =SUM(COUNTIF(B8:CU8,{1,2})) Then: =SUBTOTAL(9,CV8:CV30000) -- Biff Microsoft Excel MVP "qh8519a" wrote in message ... I would like to count the total number of "1"s and "2"s across a range of (b8:cu30000). I have an autofilter set up and I would only like to count the exposed rows. I would like to exclude the rows hidden by the autofilter. When I use the countif function, it includes the hidden rows. Any help would be appreciated. Thanks Drew |
Thread Tools | |
Display Modes | |
|
|