| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count all except filtered data
I have used versions of the basic formula below to count the records
remaining after application of the advanced filter: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$2:$A$19)-ROW($A$1),,1)),--($C$2:$C$1920)),--($C$2:$C$19<=50)) Is there any way to change the OFFSET part to reference the opposite: all rows HIDDEN after application of the advanced filter? I don't see this question having been asked before, which is surprising. TIA |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count all except filtered data
Maybe just COUNTA the whole range you're interested in and then just subtract
your filtered result...... Vaya con Dios, Chuck, CABGx3 "andy62" wrote: I have used versions of the basic formula below to count the records remaining after application of the advanced filter: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$2:$A$19)-ROW($A$1),,1)),--($C$2:$C$1920)),--($C$2:$C$19<=50)) Is there any way to change the OFFSET part to reference the opposite: all rows HIDDEN after application of the advanced filter? I don't see this question having been asked before, which is surprising. TIA |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count all except filtered data
I don't see this question having been asked before
which is surprising. I've never seen it either! The formula counts all visible rows that meet the condition. So, if you want the "opposite", wouldn't it just be the total count of all rows minus the result of your formula? -- Biff Microsoft Excel MVP "andy62" wrote in message ... I have used versions of the basic formula below to count the records remaining after application of the advanced filter: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$2:$A$19)-ROW($A$1),,1)),--($C$2:$C$1920)),--($C$2:$C$19<=50)) Is there any way to change the OFFSET part to reference the opposite: all rows HIDDEN after application of the advanced filter? I don't see this question having been asked before, which is surprising. TIA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNT UNIQUE ENTRIES IN FILTERED DATA | Excel Discussion (Misc queries) | |||
| Autofilter and count on filtered data | Excel Worksheet Functions | |||
| count filtered data | Excel Discussion (Misc queries) | |||
| Count certain records in filtered data | Excel Discussion (Misc queries) | |||
| count specific value with filtered data | Excel Worksheet Functions | |||
Linear Mode
