sumproduct isnumber ignoring blanks & duplicate cells

[Rodrigues]

Hi All
I have a file (sample attached) where I'm using the following formula in C1:
=SUMPRODUCT(--ISNUMBER((A1:A19))), returns 18 and I would like to see 11.
I need to ignore the blank cells and duplicate numbers.
Can anyone help me with this one please?
many thanks in advance.
Regards
Rodrigues

 

[Rodrigues]

Hi All
I have a file (sample attached) where I'm using the following formula in C1:
=SUMPRODUCT(--ISNUMBER((A1:A19))), returns 18 and I would like to see 11.
I need to ignore the blank cells and duplicate numbers.
Can anyone help me with this one please?
many thanks in advance.
Regards
Rodrigues

All
Forgotten to say that, the source range will be on another file not in book2.
Thanks
Rodrigues

 

[jayalaxmi]

try this

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

regards,
Jaya

 

[Rodrigues]

Jaya
That's perfect.
Thank you very much for your help.
Regards
Rodrigues

 

[jayalaxmi]

Jaya
That's perfect.
Thank you very much for your help.
Regards
Rodrigues

welcome

u can try this too...with CSE

=SUM(IF(FREQUENCY(IF(LEN(A1:A19)>0,MATCH(A1:A19,A1:A19,0),""), IF(LEN(A1:A19)>0,MATCH(A1:A19,A1:A19,0),""))>0,1))

 

[Khalid NGO]

Hi,
another one, similar to Jaya:
=SUM(SIGN(FREQUENCY(A1:A19,A1:A19)))

Regards,