count
 

I want to count how many different customers are in a column. I have a lot
of duplicate customers, so I need a formula that will tell me how many
different ones there are. In my example below the total count is 4 in a
total of 5 lines.

I.E.
adva
palm
palm
john
teak

count
 

Hi,

Try this

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

Mike

"Carla" wrote:

I want to count how many different customers are in a column. I have a lot
of duplicate customers, so I need a formula that will tell me how many
different ones there are. In my example below the total count is 4 in a
total of 5 lines.

I.E.
adva
palm
palm
john
teak

count
 

One way...

If there might be empty cells within the range:

=SUMPRODUCT((A2:A6<"")/COUNTIF(A2:A6,A2:A6&""))

If there won't be any empty cells within the range:

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

--
Biff
Microsoft Excel MVP


"Carla" wrote in message
...
I want to count how many different customers are in a column. I have a lot
of duplicate customers, so I need a formula that will tell me how many
different ones there are. In my example below the total count is 4 in a
total of 5 lines.

I.E.
adva
palm
palm
john
teak

count
 

I tried that formula with a column showing numbers 1-10 with your formula,
and it said I only have 9 different items when it should be 10.

Now, when I did it with words, it worked correctly.

Thank you,
Carla

"Mike H" wrote:

Hi,

Try this

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

Mike

"Carla" wrote:

I want to count how many different customers are in a column. I have a lot
of duplicate customers, so I need a formula that will tell me how many
different ones there are. In my example below the total count is 4 in a
total of 5 lines.

I.E.
adva
palm
palm
john
teak

count
 

You sure you don't have a repeat of one of those numbers?

Returns 10 for me with 1 through 10 in A1:A10


Gord Dibben MS Excel MVP

On Wed, 4 Nov 2009 12:38:01 -0800, Carla
wrote:

I tried that formula with a column showing numbers 1-10 with your formula,
and it said I only have 9 different items when it should be 10.

Now, when I did it with words, it worked correctly.

Thank you,
Carla

"Mike H" wrote:

Hi,

Try this

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

Mike

"Carla" wrote:

I want to count how many different customers are in a column. I have a lot
of duplicate customers, so I need a formula that will tell me how many
different ones there are. In my example below the total count is 4 in a
total of 5 lines.

I.E.
adva
palm
palm
john
teak

count
 

There are many ways to count uniques among duplicates:
=SUMPRODUCT((A2:A105<"")/(COUNTIF(A2:A105,A2:A105&"")))
=SUMPRODUCT((A2:A998<"")/(COUNTIF(A2:A998,A2:A998)+(A2:A998="")))
=SUM(IF(FREQUENCY(IF(LEN(A2:A999)0,MATCH(A2:A999, A2:A999,0),""),IF(LEN(A2:A999)0,MATCH(A2:A999,A2: A999,0),""))0,1))
Note: This is a CSE Function
=SUMPRODUCT((A2:A105<"")/COUNTIF(A2:A105,A2:A105&"")*(A2:A105<""))
=SUMPRODUCT(--(A2:A999<""),1/COUNTIF(A2:A999,A2:A999&""))
=SUM(--(FREQUENCY(IF(A2:A2705<"",MATCH(A2:A2705,A2:A2705 ,0)),ROW(INDIRECT("1:"&ROWS(A2:A2705))))0))
Note: This is a CSE Function
=COUNT(1/FREQUENCY(A1:A400,A1:A400))
=SUM(1/COUNTIF(C13:C17,C13:C17))
Note: This is a CSE Function & it fails if there are any blanks in between
=SUM(IF(A2:A400<"",1/COUNTIF(A2:A400,A2:A400)))
=SUM(IF(FREQUENCY(IF(A2:A1001<"",MATCH("~"&A2:A10 01,A2:A1001&"",0)),ROW(A2:A1001)-ROW(A2)+1),1))

=IF(COUNT(A1:A20)<ROW(A1),"",INDEX(A1:A20,MATCH(SM ALL(A1:A20,ROW(A1)),A1:A20,0)))


HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Carla" wrote:

I tried that formula with a column showing numbers 1-10 with your formula,
and it said I only have 9 different items when it should be 10.

Now, when I did it with words, it worked correctly.

Thank you,
Carla

"Mike H" wrote:

Hi,

Try this

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

Mike

"Carla" wrote:

I want to count how many different customers are in a column. I have a lot
of duplicate customers, so I need a formula that will tell me how many
different ones there are. In my example below the total count is 4 in a
total of 5 lines.

I.E.
adva
palm
palm
john
teak