countif

[ROBERT MARCHETTI]

if you have a range of numbers running down in order, how can you use above formula to see when that number changes, so e.g if you have 1,1,1,1,2,2,2,3,3,3 and you only want to know how many different numbers you have e.g 3

 

[Aladin Akyurek]

On 2002-05-06 02:57, ROBERT MARCHETTI wrote:
if you have a range of numbers running down in order, how can you use above formula to see when that number changes, so e.g if you have 1,1,1,1,2,2,2,3,3,3 and you only want to know how many different numbers you have e.g 3

For a range with no blanks and of numeric type, you can use:

=SUM(IF(FREQUENCY(A1:A15,A1:A15)>0,1))

otherwise you need an array-formula:

=SUM(IF(LEN(A1:A15),1/(COUNTIF(A1:A15,A1:A15))))

Aladin

 

[ROBERT MARCHETTI]

thanks 1st works perfect!