Hello

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,218

Office Version

1. 365
2. 2010

Platform

1. Windows

• Aug 23, 2011

• #2

kusgh said:

I want to count all the unique entries in a column. I have more than 5000 entries in a column. Eg.

A
A
D
C
C
C
E
A

I want to know there are 3 A's, 1 D, 3 C's, 1 E. I need the count as to how many times each unique entry appears in the column.

Any help wud be appreciated!

Click to expand...

Look at the COUNTIF function in Excel Help.

 

Upvote 0

K

kusgh

New Member

Joined

Aug 23, 2011

Messages

9

• Aug 23, 2011

• #3

The problem is i have more than 1000 unique entries. I am not able to find if COUNTIF works for more than one unique entry.

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Aug 23, 2011

• #4

What result do you want from the data in the OP.

"How many times each unique entry appears in the column" = 1 (by definition)?
or
2 (D and E are the only unique entries)
or
do you need help in extracting a list of all the entries in column A, with no duplicates.
That list would be used with teh COUNTIF function to count each of them.
Have you looked at AdvancedFilter's Unique Only option?

 

Upvote 0

K

kusgh

New Member

Joined

Aug 23, 2011

Messages

9

• Aug 23, 2011

• #5

lemme give u a full description of what I need:

A 1
A 3
C 1
D 1
B 2
B 3
A 1
E 4
E 2
B 1

Now I want to get that the sum of all the A's is 5, B's is 6, C is 1, D is 1and E's is 6.

Sorry for the incomplete description before.

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Aug 23, 2011

• #6

SUMIF will do that.

 

Upvote 0

K

kusgh

New Member

Joined

Aug 23, 2011

Messages

9

• Aug 23, 2011

• #7

The problem is I have over 5000 entries with more than 1000 unique ones. Is there some other way to do it??

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Aug 23, 2011

• #8

kusgh said:

The problem is I have over 5000 entries with more than 1000 unique ones. Is there some other way to do it??

Click to expand...

To do what?

To create the list of unique items?

This formula will not do that. AdvancedFilter, with its Copy to other location and Unique only options, would be the way to do that.

Once that list exists, SUMIF formulas in the next column are the best way to do that.

You also might look at a Pivot Table to see if it will do what you want.

 

Upvote 0