Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excluding Hidden Rows from calculations | Excel Worksheet Functions | |||
Sum of a column excluding hidden rows | Excel Worksheet Functions | |||
Counting non-blank cells in a column, excluding hidden rows | Excel Worksheet Functions | |||
paste excel sheet excluding hidden rows | Excel Discussion (Misc queries) | |||
Countif and hidden rows. | Excel Worksheet Functions |