Could anyone please help me with a formula that would count how many
unique entries are in a given range of cells. All I need to know is
how many different entries there are. (Not including empty cells)

So if I have A2:A20,

A2 Hello
A3 Goodbye
A4 Hello
A5 Cya
A6 L8R
A7 Hello
A8 Goodbye

and so on and so on...I want a formula to tell me that there are 4
different entries.

Any help would be great!

Try looking here
http://www.cpearson.com/excel/duplic...gingDuplicates
http://www.cpearson.com/excel/duplic...tingDuplicates

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"ChuckF" wrote in message
oups.com...
Could anyone please help me with a formula that would count how many
unique entries are in a given range of cells. All I need to know is
how many different entries there are. (Not including empty cells)

So if I have A2:A20,

A2 Hello
A3 Goodbye
A4 Hello
A5 Cya
A6 L8R
A7 Hello
A8 Goodbye

and so on and so on...I want a formula to tell me that there are 4
different entries.

Any help would be great!

http://www.cpearson.com/excel/duplic...CountingUnique

these are often array formulae, so you may need to press CTRL-ALT-ENTER
instead of just enter after you have typed them.

Otherwise I am sure this will sort you out.
--
Allllen


"ChuckF" wrote:

Could anyone please help me with a formula that would count how many
unique entries are in a given range of cells. All I need to know is
how many different entries there are. (Not including empty cells)

So if I have A2:A20,

A2 Hello
A3 Goodbye
A4 Hello
A5 Cya
A6 L8R
A7 Hello
A8 Goodbye

and so on and so on...I want a formula to tell me that there are 4
different entries.

Any help would be great!

When faced with this problem, I use Jim Cone's fine commercial add-in called
XL Companion. It's available at

http://www.realezsites.com/bus/primitivesoftware/

Vaya con Dios,
Chuck, CABGx3




"ChuckF" wrote:

Could anyone please help me with a formula that would count how many
unique entries are in a given range of cells. All I need to know is
how many different entries there are. (Not including empty cells)

So if I have A2:A20,

A2 Hello
A3 Goodbye
A4 Hello
A5 Cya
A6 L8R
A7 Hello
A8 Goodbye

and so on and so on...I want a formula to tell me that there are 4
different entries.

Any help would be great!

Try this:

=SUMPRODUCT(--(A2:A8<""),--(1/COUNTIF(A2:A8,A2:A8)))


"ChuckF" wrote:

Could anyone please help me with a formula that would count how many
unique entries are in a given range of cells. All I need to know is
how many different entries there are. (Not including empty cells)

So if I have A2:A20,

A2 Hello
A3 Goodbye
A4 Hello
A5 Cya
A6 L8R
A7 Hello
A8 Goodbye

and so on and so on...I want a formula to tell me that there are 4
different entries.

Any help would be great!

But if the list might contain blank cells, you'd need something like this:

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Teethless mama" wrote in message
...
Try this:

=SUMPRODUCT(--(A2:A8<""),--(1/COUNTIF(A2:A8,A2:A8)))


"ChuckF" wrote:

Could anyone please help me with a formula that would count how many
unique entries are in a given range of cells. All I need to know is
how many different entries there are. (Not including empty cells)

So if I have A2:A20,

A2 Hello
A3 Goodbye
A4 Hello
A5 Cya
A6 L8R
A7 Hello
A8 Goodbye

and so on and so on...I want a formula to tell me that there are 4
different entries.

Any help would be great!