Counting Rows with Duplicate Data

[ralamo]

I don't know if its possible to do this in Excel. My worksheets has clients with duplicate rows. I'm attempting to tally the number of clients without counting them more than once.

i.e.:

client a
client a
client a
client b
client b
client c

total client: 3 instead of 6

Thanks in advance for your help.
Rob

 

[Aladin Akyurek]

On 2002-04-23 12:39, ralamo wrote:
I don't know if its possible to do this in Excel. My worksheets has clients with duplicate rows. I'm attempting to tally the number of clients without counting them more than once.

i.e.:

client a
client a
client a
client b
client b
client c

total client: 3 instead of 6

Thanks in advance for your help.
Rob

Either

=SUM(IF(FREQUENCY(MATCH(A2:A100,A2:A100,0),MATCH(A2:A100,A2:A100,0)),1))

or, array-enter,

=SUM(IF(LEN(A2:A100),1/COUNTIF(A2:A100,A2:A100)))

Note. To array-enter a formula hit control+shift+enter at the same time, not just enter.
This message was edited by Aladin Akyurek on 2002-04-23 12:52

 

[John McGraw]

On 2002-04-23 12:39, ralamo wrote:
I don't know if its possible to do this in Excel

Never say that! Anything is possible in Excel. I knew Aladin would get to you soon enough.
This message was edited by John McGraw on 2002-04-23 12:57

 

[ralamo]

Thank you very, very much, Aladin. The first formula worked. I will never doubt Excel ever again, John.

I'm a true believer, now.

 

[Dinictus]

Otherwise try the advanced data filter-> unique record only. Then marking duplcate rows and couting them.

Even better: use SAS to tacle these kind of things.