Count non blank cells

[hsandeep]

I get values in a column R (R18:R9999) which are either

1. values (alphabets, numericals or alphanumericals) or
2. null "" throgh formula.

How to count no. of cells where values (serial no. 1) appear?
Please someone help.

 

[Firefly2012]

Hi

Perhaps:

=ROWS(R18:R9999) - COUNTBLANK(R18:R9999)

 

[Robert Mika]

or maybe
=SUMPRODUCT(--(LEN(R18:R9999)>0))

 

[T. Valko]

I get values in a column R (R18:R9999) which are either

1. values (alphabets, numericals or alphanumericals) or
2. null "" throgh formula.

How to count no. of cells where values (serial no. 1) appear?
Please someone help.

If you're wanting to count the numeric values only...

=COUNT(R18:R9999)

 

[hsandeep]

Thanks Firefly2012
=ROWS(R18:R9999) - COUNTBLANK(R18:R9999) WORKED.
Will it also be applicable if any cell value = #N/A?

 

[Firefly2012]

Thanks Firefly2012
=ROWS(R18:R9999) - COUNTBLANK(R18:R9999) WORKED.
Will it also be applicable if any cell value = #N/A?

Countblank won't count #N/A error values in cells - if you want the errors excluded too then perhaps an array formula like:

=COUNT(IF(1-ISERROR(R18:R9999),IF(R18:R9999<>"",1)))

which must be confirmed with Ctrl+Shift+Enter

 

[hsandeep]

It gave me answer as 1. This is WRONG! Is there some mistake in the formula? It should not count null "" generated through formula or #N/A.

 

[Firefly2012]

Can you post the exact formula you are using please - copy it in from the Formula Bar. Did you confirm it with Ctrl+Shift+Enter (not just enter) and was the formula in the formula surrounded with curly braces {} which denote sucessful array formula entry?

 

[T. Valko]

It gave me answer as 1. This is WRONG! Is there some mistake in the formula? It should not count null "" generated through formula or #N/A.

How about this...

=COUNTIF(R18:R9999,"?*")+COUNT(R18:R9999)

 

[hsandeep]

Sir, It WORKS.
BUT, it should not count null "" generated through formula or #N/A. Is it like that?
Plus
a blank left in the column R18:R9999, it should not count. I hope it will not count. Will it?