Count Unique with criteria condition

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