Counting Cells, excluding those which have appeared before.

[BigMac]

Hi there,

I'm wondering how I'd be able to have a formula to calculate the number
of cells in a row which have appeared once and even though the value
appears many times in that row, I'd only count it as once. For
instance:

A
----
M
M
N
E
F
G
T
S
T
S

The result I'd want is 7, not taking into consideration the repeated
alphabets M, T and S. I wish to only count each alphabet once, not
matter whether if they are repeated or not.

Thanks!

 

[Niek Otten]

http://www.cpearson.com/excel/duplicat.htm#CountingUnique

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi there,
|
| I'm wondering how I'd be able to have a formula to calculate the number
| of cells in a row which have appeared once and even though the value
| appears many times in that row, I'd only count it as once. For
| instance:
|
| A
| ----
| M
| M
| N
| E
| F
| G
| T
| S
| T
| S
|
| The result I'd want is 7, not taking into consideration the repeated
| alphabets M, T and S. I wish to only count each alphabet once, not
| matter whether if they are repeated or not.
|
| Thanks!
|

 

[BigMac]

My god this is a godsend. I really don't know what I've done without
this. Thank you so much! It's guys like you that make EXCEL so much
easier to use. Thanks again!

 

[Jim May]

In say cell C1 type in:
=SUM(1/COUNTIF(A1:A10,A1:A10)) <<Do Not Press Enter As Usual; Instead
Press (the 3 keys simultaneously) Ctrl + Shift + Enter -- This should
"Add" internally two { } characters - ONe at the beginng and one at the end
These {} can not be entered at the keyboard.

HTH