Counting blank rows in Excel, not blank cells

Solution 1:

VBA is not required. Helper columns are not required. Even array entering a formula is not required!

Enter the following formula in H2:

=SUM(--(MMULT(--(A2:F11<>""),ROW(INDEX(H:H,1):INDEX(H:H,COLUMNS(A2:F11))))=0))

The formula works by multiplying the matrix of the check for non-blank of the entire range with a column vector of non-zeroes of the same length as the number of columns of the range. This results in a column vector of values which are greater than zero if any of the cells of the appropriate row is non-blank. Summing the check for zero of each of these values results in the number of blank rows.

Solution 2:

How it works:

• In Cell E2 write this Formula: =QUOTIENT(COUNTBLANK(B2:D2),3)& fill it down.

Note, 3 represents "total columns" in data range, like B, C & D are 3. You can change it as many columns included in data rage.

• Write this formula in Cell E11 =Sum(E2:E10)