Count number of different entries?

[Guest]

Hello-

Is there a simple way to count the number of different entries in a column?

ex. I have multiple rows for the same "salesperson". I have many
salespersons. I want to know how many salespersons I have.

I guess I'm counting non-duplicate entries in a column.

Thank you,
I'm learning lots!

 

[Guest]

This is an array formula, so enter it with Ctrl+Shift+Enter

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

 

[Ron Rosenfeld]

Hello-

Is there a simple way to count the number of different entries in a column?

ex. I have multiple rows for the same "salesperson". I have many
salespersons. I want to know how many salespersons I have.

I guess I'm counting non-duplicate entries in a column.

Thank you,
I'm learning lots!

Depends on whether or not there are blanks in the range.

If no blanks:

=SUM(1/COUNTIF(A1:A10,A1:A10))

entered as an *array* formula.

If there may be blanks, even if they are at the end of the range, then:

=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),
IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))

also entered as an *array* formula.

To enter an *array* formula, after typing or pasting the formula into the
formula bar, hold down <ctrl><shift> while hitting <enter>. Excel will place
braces {...} around the formula.


--ron