count cells with unique content

[richardcdahlgren]

I am trying to count cells that have unique values. For instance, cell A1 has JOE, A2 has JOE, A3 has JOE, A4 has MATT, A5 has MATT, A6 has TOM.

I need a formula that will count the unique cells so that the total from aboves example is 3 different people (Joe is 1, Matt is 1, and Tom is 1).

Any assistance is greatly appreciated.

 

[Matty]

Hi,

If your data set is relatively small, try:

=SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))

If it is large, there are other methods we can employ which will be more efficient.

Matty

 

[richardcdahlgren]

I got that to work, now a minor twist - I have a cell that has "none" and I don't want that counted.

How do I count just the names and not the "none"?

 

[richardcdahlgren]

And thank you Matty for your help. I was pulling my hair out.

 

[Matty]

Try:

=SUMPRODUCT((A1:A6<>"none")/COUNTIF(A1:A6,A1:A6))

Matty

 

[Aladin Akyurek]

I got that to work, now a minor twist - I have a cell that has "none" and I don't want that counted.

How do I count just the names and not the "none"?

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A11<>"",MATCH("~"&A2:A11,A2:A11&"",0)),
    ROW(A2:A11)-ROW(A2)+1),1))-ISNUMBER(MATCH("None",A2:A11,0))

which is a tad faster.

 

[Matty]

Would this set up be even better, Aladin?

=SUM(IF(FREQUENCY(IF(A1:A6<>"none",MATCH(A1:A6,A1:A6,0)),ROW(A1:A6)-ROW(A1)+1),1))

Assuming no blanks or perculiar characters are present, clearly.

Matty

 

[T. Valko]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A11<>"",MATCH("~"&A2:A11,A2:A11&"",0)),
ROW(A2:A11)-ROW(A2)+1),1))-ISNUMBER(MATCH("None",A2:A11,0))
which is a tad faster.

Not on the small range you're using.

On a range of just 10 cells both formulas have identical calculation times.

On a small range I'd use the much shorter and much less complicated:

=SUMPRODUCT((A2:A11<>"none")/COUNTIF(A2:A11,A2:A11))

 

[Aladin Akyurek]

Would this set up be even better, Aladin?

=SUM(IF(FREQUENCY(IF(A1:A6<>"none",MATCH(A1:A6,A1:A6,0)),ROW(A1:A6)-ROW(A1)+1),1))

Assuming no blanks or perculiar characters are present, clearly.

Matty

Matty,

The assumption of no empty cells or cells with formula blanks is more often than not violated. Another reason for including the A1:A6<>"" test is a consideration for others who would "copy" the formula: We would want them to have the full expression.

Assuming no peculiar characters would be more often valid: Including the provision would work too.

Aladin

 

[T. Valko]

Matty,

The assumption of no empty cells or cells with formula blanks is more often than not violated. Another reason for including the A1:A6<>"" test is a consideration for others who would "copy" the formula: We would want them to have the full expression.

Assuming no peculiar characters would be more often valid: Including the provision would work too.

Aladin

Trying to account for conditions which may not exist leads to excessively long, over complicated and inefficient formulas.

If the poster doesn't specifically state or present sample data that such conditions exist then it would be a best practice to use the minimum formula that does the job.

If a poster replies back and then adds special conditions then those conditions can be accounted for at that time.

KISS