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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

McGuilliam

New Member
Joined
Oct 23, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
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.
 

p86c

New Member
Joined
Jun 13, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,888
Office Version
  1. 365
Platform
  1. Windows
If you point this formula at a "blank" cell what does it return?
Excel Formula:
=CODE(A2)
 

p86c

New Member
Joined
Jun 13, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

If you point this formula at a "blank" cell what does it return?
Excel Formula:
=CODE(A2)
It says #VALUE!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,888
Office Version
  1. 365
Platform
  1. Windows
That suggest that the cell is empty, how about these two
Excel Formula:
=LEN(A2)
=ISBLANK(A2)
 

p86c

New Member
Joined
Jun 13, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

That suggest that the cell is empty, how about these two
Excel Formula:
=LEN(A2)
=ISBLANK(A2)
len gives 0
isblank gives false
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,888
Office Version
  1. 365
Platform
  1. Windows
Ok, that sounds like it might be a null string, which column are these in?
Also are there any formulae in that column?
 

p86c

New Member
Joined
Jun 13, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
multiple columns - no formulae though
 

p86c

New Member
Joined
Jun 13, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,278
Messages
5,674,807
Members
419,526
Latest member
ranjit446

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
Top