BWA@CSFB

Hi,

Is there a function that will allow you to count the number of individual entries in a column.

Say for instance a column as the following:

123
123
155
144
144
144

Can I get it to return a value of 3 to reflect that although there are 6 figures in the column, there is only 3 individual entries?

Cheers,

Bren.

ReeKorl

There's a few ways of doing this depending on how much work you want to put in.

The first (and most manual method) is sorting the column into numeric order. In the column next to this, put the formula:

(this assumes the data is in column A from cell A1 downwards)

This formula needs to go into cell B2, and autofilled downwards to the end of the data.

One cell below this new column, you need a formula showing:

This shows how many duplicate entries there are. To the left of this, put this formula:

This will subtract the duplicate entries from the total entries.

Alternatively there's a method using the Advanced Filter functionality, but it's as cumbersome as the last method for no real benefit.

The final (and most automatic) method involves programming a custom VB function to do this for you. If the above method isn't good enough, let me know and I'll see what I can do for you.

PRP

Hello,

1) Add extra row above ur all datas Say DATA1 as shown below:

DATA1
123
123
155
144
144
144

2) Sort DATA1 in Ascending order

3) After Sorting, go to "Subtotal" in Data menu
Now you will see,

At each change in : DATA1
Use Function : Count

Say OK

You will see the result that you wanted,

Cheers,

P R PATEL

dheerajnagpal

A better way to count the number of unique entries is

enter all entries in a column, need not be in sorted order
at bottom, in a blank cell, press F2 to go in edit mode,
enter the formula
=SUM(IF(FREQUENCY(A3:A25, A3:A25)>0,1))
assuming that A3:A25 is the range.
Then press CTRL + SHIFT + RETURN
Remember to use CTRL and SHIFT as this makes it an array formula instead of a cell formula. You will get the count of unique number of items in the column

Cheers
Dheeraj