HomeGoogle DocsSpreadsheetHow to Count Until a Blank Row in Google Sheets

How to Count Until a Blank Row in Google Sheets

Published on

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.

How to Count Until a Blank Cell in Google Sheets

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:

only number cells that is blank in Google sheets

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!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here