| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique values in a filtered range
I would like a unique count to return in a filtered range the same way I have
a total returned using a subtotal function (Subtotal(9,range)). This would be on a unique idenitier for each row. Is there any way to do this in a formula, as I'd like this to be easy to use for others? I don't want to use the Advanced filter. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique values in a filtered range
Try..
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW (A2:A100)-ROW(A2),0,1)) ,MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1)0,1)) ....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly. Hope this helps! In article , ChrisR wrote: I would like a unique count to return in a filtered range the same way I have a total returned using a subtotal function (Subtotal(9,range)). This would be on a unique idenitier for each row. Is there any way to do this in a formula, as I'd like this to be easy to use for others? I don't want to use the Advanced filter. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique values in a filtered range
Thank you , thank you!!!!
"Domenic" wrote: Try.. =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW (A2:A100)-ROW(A2),0,1)) ,MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1)0,1)) ....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly. Hope this helps! In article , ChrisR wrote: I would like a unique count to return in a filtered range the same way I have a total returned using a subtotal function (Subtotal(9,range)). This would be on a unique idenitier for each row. Is there any way to do this in a formula, as I'd like this to be easy to use for others? I don't want to use the Advanced filter. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNT UNIQUE ENTRIES IN FILTERED DATA | Excel Discussion (Misc queries) | |||
| How do I count unique values within a date range? | Excel Discussion (Misc queries) | |||
| Count Unique Values In A Filtered Row with Duplicates | Excel Discussion (Misc queries) | |||
| Count unique values among duplicates in a subtotal range | Excel Discussion (Misc queries) | |||
| Unique Count sensitive to hidden/filtered rows | Excel Worksheet Functions | |||
Linear Mode
