Count If Unique -- help

**exceldave84** · 08-03-2011, 10:02 AM

Hey all,

I need some help with this problem --I'm sure it's much simpler than I realize, but here it is:

I need to count the number of unique items in a column. Example:

----- A
1 -- 1213
2 -- 1213
3 -- 5228
4 -- 2217

In this simple table there are 4 rows but only 3 different types of numbers (1213, 5228 & 2217). I need a formula that counts repeating text/numbers only once.

Does anyone have any thoughts?

**Colin Legg** · 08-03-2011, 10:06 AM

Welcome to the forum.

If the range contains only numbers you can use this formula:
=SUMPRODUCT(--(FREQUENCY(A1:A4,A1:A4)>0))

If the range contains a mixture of numbers and text you can use this formula (this is an array formula so when you type it into the formula bar you must complete the entry with CTRL+SHIFT+ENTER, not just ENTER):
=SUM(IF(FREQUENCY(IF(A1:A4<>"",MATCH("~"&A1:A4,A1:A4&"",0)),ROW(A1:A4)-ROW(A1)+1),1))

Both sourced from here.

**Ron Coderre** · 08-03-2011, 10:56 AM

This regular formula counts the unique items (text or numeric)
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Is that something you can work with?