Link to home
Start Free TrialLog in
Avatar of mato01
mato01Flag for United States of America

asked on

Counting Unique Values with Criteria on Filtered Column

I was given this solution recently to count the unique values in a range when the values in the cell did not contain the word (NULL) or was not blank.

I'd like to take it a little further, but could not find any solutions.

=SUM(IF(FREQUENCY(IF(LEN(SUBSTITUTE(F13:F10000,"(NULL)",""))>0,MATCH(F13:F10000,F13:F10000,0),""),IF(LEN(SUBSTITUTE(F13:F10000,"(NULL)",""))>0,MATCH(F13:F10000,F13:F10000,0),""))>0,1))


My question is whether this formula could be manipulated to work if the column is filtered.

Examples for Column F

Unfiltered Data would count 4 unique values.

Column F (Formula NOW)

Peaches
Oranges
(NULL)
Grapes
Apples
Peaches
Oranges

Filtered Data (filtering out Peaches) would then count 3 unique values.

Column F (Formula DESIRED)

Oranges
(NULL)
Grapes
Apples
Oranges

The original formula would still count 4 because it ignores the filtering.
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mato01

ASKER

Perfect. Thank you.