Formula

[-emma-]

Hi All,

What i want to do sounds sooooo easy...yet it has had me stumped for a few days now . Ok...here goes...

I have a column which has numbers in it. All i need is a formula to work out how many different numbers the column holds.
EG

21
45
44
45
45
44
21
36

The answer should be 4 as there is 4 different numbers (21, 36, 44 & 45).

Any sugestions would be helpful.

Thank You

[Bryan Hessey]

Hi All,

What i want to do sounds sooooo easy...yet it has had me stumped for a few days now . Ok...here goes...

I have a column which has numbers in it. All i need is a formula to work out how many different numbers the column holds.
EG

21
45
44
45
45
44
21
36

The answer should be 4 as there is 4 different numbers (21, 36, 44 & 45).

Any sugestions would be helpful.

Thank You

The easy way would be to use a helper column, in B1 put either

=IF(COUNTIF(A$1:A1,A1)>1,"",A1)

or

=IF(COUNTIF(A1:A$8,A1)>1,"",A1)

to show either the first or last occurance of a number, then formula-fill that down the column as far as your numbers.

=Count(B1:B8) will count 4 items.

hth
---

[-emma-]

This works great but...there is always a but...

What if the column was constantly getting added to? I would like it extend the B column to be ready for when i new entry is to be added and also make use the "=Count" part is extended past the end entry. When i do this it messes up the cound and i get minus figures
Any other ideas?

[Bryan Hessey]

This works great but...there is always a but...

What if the column was constantly getting added to? I would like it extend the B column to be ready for when i new entry is to be added and also make use the "=Count" part is extended past the end entry. When i do this it messes up the cound and i get minus figures
Any other ideas?

What I do there is to have data in 1 to 8, put the =Count(B1:B9) in B10 (includes 1 blank row) then Insert at row 9 and the Count will increase with it (note, the blank isn't necessary, it works on Insert, it just looks better)

hth
---

[-emma-]

im full of problems tonight...

IF it was just me adding the entires in the "Insert Row" would be fine...but the silly ones I work with wouldnt be able to grasp it. Ill work round it and see what I can do. Thanks anyway.

[Bryan Hessey]

im full of problems tonight...

IF it was just me adding the entires in the "Insert Row" would be fine...but the silly ones I work with wouldnt be able to grasp it. Ill work round it and see what I can do. Thanks anyway.

put the total in D1

=count(B:B)


Better option, in B2 (NOT B1 - leave the old formula in B1) put the formula

=IF(AND(ROW()>1,INDIRECT("A"&ROW())="",INDIRECT("a"&ROW()-1)<>""),COUNT(INDIRECT("B1:B"&ROW()-1)),IF(COUNTIF(A2:A$8,A2)>1,"",A2))

and in Tools, Options, View, untick 'Zero Values'
---
the sub-total will move down the page as entries are made in A
---

[-emma-]

Still doesnt work

I have typed in column A numbers and cells 1 - 10 are full but for formula purposes ive used A1-A20 to acount for new entires. I have the "=IF(COUNTIF(A$1:A1,A1)>1,"",A1)" copied in each cell from B1-B20. The count would work if i took out the formla in B11-B20 as it returns a "0" figure which is included in the "=count(B:B)" in cell D1. I am getting the result as 14 when i really want 12. I am about to try and minus off the blank cells in column A by using the COUNTBLANK formula. Ill message back if im still stuck.

[-emma-]

I fixed it

I used "=COUNT(B:B)-COUNTBLANK(A1:A20)" in D1.

Thanks for all your hard work Bryan.

[Bryan Hessey]

I fixed it

I used "=COUNT(B:B)-COUNTBLANK(A1:A20)" in D1.

Thanks for all your hard work Bryan.

check the 'better' option from B2 downwards, and ignore D1

corrected to:

=IF(AND(ROW()>1,INDIRECT("A"&ROW())="",INDIRECT("a"&ROW()-1)<>""),COUNT(INDIRECT("B1:B"&ROW()-1)),IF(COUNTIF(A$2:A2,A2)>1,"",A2))

(to display the first occurance)