# Count non blank cells

#### hsandeep

##### Well-known Member
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?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi

Perhaps:

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

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

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?
If you're wanting to count the numeric values only...

=COUNT(R18:R9999)

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

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

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.

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?

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.

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

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?

Replies
3
Views
168
Replies
8
Views
134
Replies
4
Views
67
Replies
2
Views
246
Replies
1
Views
176

1,203,683
Messages
6,056,728
Members
444,887
Latest member
cvcc_wt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back