Count unique entries...
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! |
Count unique entries...
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! |
Count unique entries...
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! |
Count unique entries...
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! |
Count unique entries...
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! |
Count unique entries...
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! |
All times are GMT +1. The time now is 11:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com