I need to count the number of customers listed in a certain column. They can
appear multiple times but I just want to count each different customer once

thanks

In article ,
Curtis wrote:

I need to count the number of customers listed in a certain column. They can
appear multiple times but I just want to count each different customer once

thanks


For a unique count, try the following formula that needs to be confirmed
with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(A2:A100<"",MATCH("~"&A2:A100 ,A2:A100&"",0)),ROW(A2:
A100)-ROW(A2)+1),1))

Adjust the ranges, accordingly.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

Hi,

You may try this formula

=sumproduct((1/countif(range.range))

This assumes that there are no blank cells in the range.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Curtis" wrote in message
...
I need to count the number of customers listed in a certain column. They
can
appear multiple times but I just want to count each different customer
once

thanks

There are so many ways to do this:
< -- =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))

HTH,
Ryan--

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


"Ashish Mathur" wrote:

Hi,

You may try this formula

=sumproduct((1/countif(range.range))

This assumes that there are no blank cells in the range.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Curtis" wrote in message
...
I need to count the number of customers listed in a certain column. They
can
appear multiple times but I just want to count each different customer
once

thanks