| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtered Counting
Hello I found an excellent formula to count unique values in a given column
and it even modifies the count when they are filtered. Here's the formula I am using for this... =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0)) I want to modify this to actually count all non-blanks (even duplicates) BUT I want to subtract all the entries of "No Data Here" and "Sold Out" So The formula gives me 1114 Unique entries and it adjusts when filtered. But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold Out" even when filtered. Is this possible? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtered Counting
Not sure I follow you on this.
You want the count of all entries in the filtered or unfiltered range less the count of "No Data Here" and "Sold Out" ? -- Biff Microsoft Excel MVP "Rob" wrote in message ... Hello I found an excellent formula to count unique values in a given column and it even modifies the count when they are filtered. Here's the formula I am using for this... =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0)) I want to modify this to actually count all non-blanks (even duplicates) BUT I want to subtract all the entries of "No Data Here" and "Sold Out" So The formula gives me 1114 Unique entries and it adjusts when filtered. But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold Out" even when filtered. Is this possible? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtered Counting
Biff, Thanks for your reply... Yes, That is exactly what I am trying to do.
"T. Valko" wrote: Not sure I follow you on this. You want the count of all entries in the filtered or unfiltered range less the count of "No Data Here" and "Sold Out" ? -- Biff Microsoft Excel MVP "Rob" wrote in message ... Hello I found an excellent formula to count unique values in a given column and it even modifies the count when they are filtered. Here's the formula I am using for this... =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0)) I want to modify this to actually count all non-blanks (even duplicates) BUT I want to subtract all the entries of "No Data Here" and "Sold Out" So The formula gives me 1114 Unique entries and it adjusts when filtered. But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold Out" even when filtered. Is this possible? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtered Counting
Try this. This example formula is counting the filtered or unfiltered range
B3:B21. Adapt to suit. =SUBTOTAL(3,B3:B21)-SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B21,ROW(B3:B21)-ROW(B3),0,1)),--(ISNUMBER(MATCH(B3:B21,{"sold out","no data here"},0)))) -- Biff Microsoft Excel MVP "Rob" wrote in message ... Biff, Thanks for your reply... Yes, That is exactly what I am trying to do. "T. Valko" wrote: Not sure I follow you on this. You want the count of all entries in the filtered or unfiltered range less the count of "No Data Here" and "Sold Out" ? -- Biff Microsoft Excel MVP "Rob" wrote in message ... Hello I found an excellent formula to count unique values in a given column and it even modifies the count when they are filtered. Here's the formula I am using for this... =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0)) I want to modify this to actually count all non-blanks (even duplicates) BUT I want to subtract all the entries of "No Data Here" and "Sold Out" So The formula gives me 1114 Unique entries and it adjusts when filtered. But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold Out" even when filtered. Is this possible? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtered Counting
Awesome!! It Worked!
Thank You Sooo Much! "T. Valko" wrote: Try this. This example formula is counting the filtered or unfiltered range B3:B21. Adapt to suit. =SUBTOTAL(3,B3:B21)-SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B21,ROW(B3:B21)-ROW(B3),0,1)),--(ISNUMBER(MATCH(B3:B21,{"sold out","no data here"},0)))) -- Biff Microsoft Excel MVP "Rob" wrote in message ... Biff, Thanks for your reply... Yes, That is exactly what I am trying to do. "T. Valko" wrote: Not sure I follow you on this. You want the count of all entries in the filtered or unfiltered range less the count of "No Data Here" and "Sold Out" ? -- Biff Microsoft Excel MVP "Rob" wrote in message ... Hello I found an excellent formula to count unique values in a given column and it even modifies the count when they are filtered. Here's the formula I am using for this... =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0)) I want to modify this to actually count all non-blanks (even duplicates) BUT I want to subtract all the entries of "No Data Here" and "Sold Out" So The formula gives me 1114 Unique entries and it adjusts when filtered. But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold Out" even when filtered. Is this possible? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtered Counting
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Rob" wrote in message ... Awesome!! It Worked! Thank You Sooo Much! "T. Valko" wrote: Try this. This example formula is counting the filtered or unfiltered range B3:B21. Adapt to suit. =SUBTOTAL(3,B3:B21)-SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B21,ROW(B3:B21)-ROW(B3),0,1)),--(ISNUMBER(MATCH(B3:B21,{"sold out","no data here"},0)))) -- Biff Microsoft Excel MVP "Rob" wrote in message ... Biff, Thanks for your reply... Yes, That is exactly what I am trying to do. "T. Valko" wrote: Not sure I follow you on this. You want the count of all entries in the filtered or unfiltered range less the count of "No Data Here" and "Sold Out" ? -- Biff Microsoft Excel MVP "Rob" wrote in message ... Hello I found an excellent formula to count unique values in a given column and it even modifies the count when they are filtered. Here's the formula I am using for this... =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0)) I want to modify this to actually count all non-blanks (even duplicates) BUT I want to subtract all the entries of "No Data Here" and "Sold Out" So The formula gives me 1114 Unique entries and it adjusts when filtered. But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold Out" even when filtered. Is this possible? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting a filtered range | Excel Discussion (Misc queries) | |||
| Help Please-Counting Filtered Rows | Excel Worksheet Functions | |||
| Counting a Filtered List | Excel Discussion (Misc queries) | |||
| counting filtered items | Excel Worksheet Functions | |||
| Counting within a filtered range | Excel Worksheet Functions | |||
Linear Mode
