Count Blank Cells Row by Row in Google Sheets (COUNTBLANK Each Row)

Published on

The COUNTBLANK function won’t autofill in each row. So we can’t use it as an array formula to count blank cells, row by row in Google Sheets.

The reason, the COUNTBLANK function itself is an array function. It returns the number of empty cells in the reference cell range.

Must Check:- Google Sheets Function Guide [Quickly Learn All Popular Functions]

Example (formula in H2):

=countblank(B2:F2)

To return the number of empty cells in the next row, i.e., B3:F3, you may require to copy-paste this formula down (H3).

But, to count blank/empty cells row by row in Google Sheets, we can code an alternative array formula using the MMULT function.

Here is our sample data and what we expect our formula to do, whether it is an array or non-array formula.

Count Blank Cells Row by Row - Array Formula

There are two expected outputs: one returns the count of empty cells in rows even if the rows are totally blank, and the other outputs only in the row that contains at least a non-blank cell.

We will start with the COUNTBLANK drag-down (copy-paste) formula.

COUNTBLANK to Count Empty Cells in Each Row (Drag Down Formula)

We were talking about the formulas that won’t expand. Here are them.

H2:

=countblank(B2:F2)

I2:

=if(len(textjoin(",",true,B2:F2)),countblank(B2:F2),)

We should copy-paste them down the column.

As you can see, I’ve additionally used an IF statement with the I2 formula to remove the COUNTBLANK results from blank rows.

Please see the non-highlighted part of the formula.

The TEXTJOIN combines values in the row. The LEN returns the length of it.

If the LEN returns any value, the IF executes the value_if_true part, i.e., the COUNTBLANK in green, else blank.

I know you are here for an array formula to count blank cells in each row (row by row). So, without wasting time, let me go to that coding part.

MMULT to Count Blank Cells Row by Row (Array Formula)

H2:

=ArrayFormula(mmult(if(B2:F14="",1,0),transpose(column(B2:F2)^0)))

The above formula returns the matrix product of two matrices where matrix # 1 is if(B2:F14="",1,0) and matrix # 2 is transpose(column(B2:F2)^0). That is the count of blank cells row by row.

MMULT for Counting Empty Cells in Each Row

Note:- When standalone use (testing the above matrices), enter them using Ctrl+Shift+Enter or use the ArrayFormula function with them.

In an earlier tutorial, we learned the use of MMULT to Expand Count Results in Google Sheets As Array Formula Does. Please check that to understand the H2 formula further.

I2:

=ArrayFormula(if(len(trim(transpose(query(transpose(B2:F14),,9^9)))),mmult(if(B2:F14="",1,0),transpose(column(B2:F2)^0)),))

When it comes to the formula in cell I2, we have used additional IF logic to test whether the row is blank or not.

That does the role of the TEXTJOIN in our earlier I2 non-array formula that returns the count of empty cells in the row.

Since the TEXTJOIN won’t expand, we have used QUERY to replace it. You can learn that technique here in detail – The Flexible Array Formula to Join Columns in Google Sheets.

That’s all. Thanks for the stay. Enjoy!

Resources

  1. How to Count Until a Blank Row in Google Sheets.
  2. How to Count If Not Blank in Google Sheets [Tips and Tricks].
  3. Not Blank as a Condition in Countifs in Google Sheets.
  4. Count From First Non-Blank Cell to Last Non-Blank Cell in a Row in Google Sheets.
  5. Count Words and Insert Equivalent Blank Rows in Google Sheets.
  6. Also, check the tag row-wise array below.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.