I’ve decided to write this tutorial realising many people require such a formula for their different use. Today I faced one such situation where I want to identify the first blank row in my data range. I realised it can do with a count. Then how to count until a blank row in Google Sheets? Here we go.
With the help of ROW, MIN and COUNT functions we can achieve this task. We can count up to the first blank cell in a column in Google Sheets. Here are that formula and Explanation.
How to Count Until a Blank Row in Google Sheets
Here I’m counting Column G until a blank cell appears in the column. See the formula in H2 which returns 5. The range is G2: G10. In this range, wherever a blank cell first appears, the formula would stop counting the cells below from that cell.
Here is the step by step explanation to know how this formula works.
Count Until a Blank Cell in Google Sheets – Formula Explanation
If you use the below formula it would return the row numbers 2 to 10 as the cell reference starts from the second row.
=ArrayFormula(ROW(G2:G10))
To explain this formula, I’ve keyed this formula in cell I2 and see the result below.
But we want to start the numbering from 1. So it would act like a serial number. To do that you can modify the above formula as below.
=ArrayFormula(ROW(G2:G10)-ROW(G2)+1)
Now we should apply a logical test. The purpose of the following logical test is to only return serial numbers when the cells are Blank. As usual, if the condition is not fulfilled, the IF formula which we are using for the logical test would return the Boolean FALSE.
=ArrayFormula(IF(G2:G10="",ROW(G2:G10)-ROW(G2)+1))
Screenshot:
Now when you use MIN function in this range, it would obviously return the value 6 as there is no other value in this rage. It’s the serial number of the blank cell. Just less 1 from this value to get the count of cells that contain values.
=ArrayFormula(MIN(IF(G2:G10="",ROW(G2:G10)-ROW(G2)+1))-1)
Hope you could understand this concept. If any doubt, do drop in comments. Enjoy!