mato01

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.

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

=SUM(IF(FREQUENCY(IF(LEN(S

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER