Counting number of non-blank cells in a range

  • Thread starter Thread starter Android
  • Start date Start date
A

Android

Hi,

=COUNTA(RawData!CT11:CT1748) used to work for me in an old Excel 2007
file.

Does not seem to work in a new file.

Am I missing a setting?

Or, is there any other simple way?

Regards...
 
What does: "Does not seem to work" mean?

Incorrect result?
Some kind of error?
The cell displays the formula and not a number?
Keyboard starts smoking?
Monitor explodes?
 
It does not give error. It counts everything, so if in my case it
gives 1738 regardless of how many cells are blank.
 
My bet is that you have something in those cells.

If you have formulas that evaluate to "", that'll be counted.

If you have a cell that contained a formula that evaluated to "" and was
converted to values, then that'll be counted, too.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

============
Then your =counta() will return what you want. And the End|Arrow keys
(End|DownArrow and the like) will stop at the spots you expect.
 
Hi,

Either the cells contain manually entered spacebars or the contain formulas
which return "", or they contain some hidden character.

It would be best to get rid of cells with spacebars in them or hidden
characters, but if you can't do that you could try something like

=SUMPRODUCT(--(LEN(TRIM(CLEAN(B1:B17)))>0))

assuming your data is in column B.
 
Perfect.

Lots of great responses here. This is the most convenient one for me.
Works great.

Thanks.
 
Glad we could help,
--
Thanks,
Shane Devenshire


Android said:
Perfect.

Lots of great responses here. This is the most convenient one for me.
Works great.

Thanks.
Click to expand...
 
Back
Top