| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values but not Filtered or Hidden Values
Ooops! Typo:
......OFFSET(I2,..... Should be: ......OFFSET(I1,..... -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... To count the unique numeric values (or dates) in a filtered range... Assume no empty cells within the range: Array entered** =COUNT(1/FREQUENCY(IF(SUBTOTAL(2,OFFSET(I2,ROW(I1:I255)-ROW(I1),)),MATCH(I1:I255,I1:I255,0)),ROW(I1:I255)-ROW(I1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Lee" wrote in message ... Hello, I'm trying to count unique values in a row and have a filter at the same time. I'm using the formula =SUM(IF(FREQUENCY(I1:I255,I1:I255)0,1)) When I use a filter it doesn't recalculate. I tried to use the subtotal 109 function but i'm appearantly not inserting it correctly. Can anyone help? Thanks, Lee |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count unique values in a filtered range | Excel Worksheet Functions | |||
| Count Unique Values In A Filtered Row with Duplicates | Excel Discussion (Misc queries) | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| Unique Count sensitive to hidden/filtered rows | Excel Worksheet Functions | |||
Threaded Mode
