| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values but not Filtered or Hidden Values
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values but not Filtered or Hidden Values
First problem is that AutoFilters don't work for rows only columns.
If in fact you are really filtering in column then you need something of the form =SUMPRODUCT(SUBTOTAL(9,OFFSET(C1:C19,ROW(1:19)-1,,1))*(C1:C19="Red")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lee" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values but not Filtered or Hidden Values
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 |
|
#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 |
| Reply |
| 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 | |||
Linear Mode
