Count Blank Cells Across an Entire Table

0

Hi everyone, 

How do I count all of the blank cells across an entire table?

I need to count cells that are completely empty and also cells that just don't output anything but can have a formula inside.

I am using COUNTBLANK() for a single column and that works to count empty cells and cells whose formulas don't output a value, but how do I count just those cells that have literally nothing inside of them?

Thanks in advance!

Answer
Discuss

Answers

0

You would need a User Defined Function (UDF) for that. Paste the code below into a standard code module (by default "Module1" before you rename it), and save the workbook as macro enabled (xlsm format).

Function CountEmpty(Rng As Range) As Long
    Dim Fun As Long
    Dim Cell As Range
    
    Application.Volatile
    For Each Cell In Rng
        If Len(Cell.Formula) = 0 Then Fun = Fun + 1
    Next Cell
    CountEmpty = Fun
End Function

Call the function from the worksheet with =CountEmpty(A1:B10).

Discuss
0

What about using a formula like this:

=ROWS(A1:A5)-COUNTA(A1:A5)

It would work for one column.

If you want it to work on multiple columns you can do this:

=(ROWS(1:5)*COLUMNS(A:B))-COUNTA(A1:B5)
Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login