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.

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.

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.