Count Unique with criteria condition

S

Spencer Hutton

I have a range of data for which i want to count the unique values. I have
seen psotings on this before, but none specific to my situation. I have a
column of data and i want to count the unique values for which the first 4
numbers are equal to a cell's value.

Cell A1 = 1234

Column B
12340001
12340002
12340001
12340002
<Blank>
12350001
12350002
12350001
12350002

the formula should return the result 2 since there are 2 unique values in
column B that start with 1234. This needs to exclude blank cells. Thank you.
 
T

T. Valko

Try this array formula** :

=COUNT(1/FREQUENCY(IF(LEFT(B2:B20,4)=A1&"",B2:B20),B2:B20))

** 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Unique Values 4
Count Unique Values in 1 Column based on Date Range in another Column 28
Count Distinct Values based on criteria 4
Count unique dates within each unique ID 6
Count values, excluding duplicates and specific values 2
Count Unique Values 4
Count unique with multiple criteria 3
Count Unique Values in a entire column, not just range 20

Top