counting blank cells in every growing table

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
I want to count the number of blank cells in A:A but only relating to my table size. Is there a way to only have the cells in my current table be counted?
Formula's I've tried so far with no luck:
VBA Code:
=countblank(A:A)
=countif(A:A,"")

Also, I know it's not VBA code, but wanted to show it nicely. VBA is an option if that is the best method.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,715
Office Version
  1. 365
Platform
  1. Windows
Why not just limit the formula to the size of the table?
 

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
Why not just limit the formula to the size of the table?
The table is currently from A9:A250 but not all the values in A are filled in. We add to this table daily and I don't want to have to change the references each day.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,715
Office Version
  1. 365
Platform
  1. Windows
Is your "table" a proper table, or just a range of cells?
If it's just a range, then convert it to a proper table and you can use something like
=COUNTBLANK(Table1[Name])
 

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

It was a table already but I did not think of calling the table as a whole, thanks Fluff.

Once again, you've made my bacon.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,715
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,384
just for fun with Power Query...
another approach
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.SelectColumns(Table.AddColumn(Table.Transpose(Source), "CountBlank", each List.Count(List.Select(Record.FieldValues(_), each _ = null))),{"CountBlank"})
in
    Result
eg.
CountCountBlank
a4
x
d
z
dr

blue table contain blanks and empty strings but only real blanks will be counted
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,118,478
Messages
5,572,354
Members
412,459
Latest member
asmi_1758
Top