How to stop counting blank cells

p86c

New Member
Joined
Jun 13, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
So I have pasted data from generated from a piece of software into my excel sheet.

One of the formula returns a value for the number of cells in a column that has data in it.

Unfortunately it is counting all the cells in the column, including the blank ones - so I am guessing they have not transferred as blank.

I find that is I delete each blank cell manually then the count works again - but there are hundreds of these to do.

I think a find an dreplace might work - but what am I finding? and what am I replacing it with...just a blank?

And pointers?
p86c
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Could you perhaps post the formula you are using right now? Out of the top of my head, maybe a countif-function where you use <>"" could do the trick.
 
Upvote 0
Could you perhaps post the formula you are using right now? Out of the top of my head, maybe a countif-function where you use <>"" could do the trick.
the formula I used was

=COUNTIFS('A2 Students'!J14:J95,"<>",'A2 Students'!$D$14:$D$95,"1")

and like I said, it is acting as if the blank cells are not blank, unless I manually delete each one - which obviously, visually, is not making any difference.
 
Upvote 0
If you point this formula at a "blank" cell what does it return?
Excel Formula:
=CODE(A2)
 
Upvote 0
That suggest that the cell is empty, how about these two
Excel Formula:
=LEN(A2)
=ISBLANK(A2)
 
Upvote 0
Ok, that sounds like it might be a null string, which column are these in?
Also are there any formulae in that column?
 
Upvote 0
Ok, that sounds like it might be a null string, which column are these in?
Also are there any formulae in that column?
Found a solution - seems to have worked

Used Find and replace
Found nothing
Replaced with N

then Found "N"
Replaced with nothing

now the formulae are working again

Thanks for taking time to help.

p86c
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top