Calculate Subtotals Up to the First Blank Cell in Google Sheets

Published on

You can use an array formula in Google Sheets to calculate subtotals up to the first blank cell and after each subsequent blank cell. Furthermore, you can control where to place the subtotals – either corresponding to the blank cell or one row above it, i.e., in the last row of each group separated by blank cells.

The benefit of the array formula is that you do not need to adjust it each time you add more data, or insert or delete rows.

Subtotals calculated up to the first blank cell and displayed against the last non-blank cell above in Google Sheets

Example: Calculate Subtotals Up to the First Blank Cell and Insert Them in Google Sheets

Assume you have values in B2:B, separated by blank rows. The blank rows may occur at specific intervals, such as every 7 rows, or at irregular intervals. You want to calculate the subtotals up to the first blank cell and after each subsequent blank cell.

In that case, use the following formula in cell C2:

=ArrayFormula(
   LET(
      range, B2:B, 
      _rows, IF(INDIRECT("B2:B"&XLOOKUP(TRUE, range<>"", ROW(range), ,0, -1)+1)="", ROW(range),), 
      _rowsE, {ROW(B2); TOCOL(_rows, 1)}, 
      seq, SEQUENCE(ROWS(_rowsE)), 
      total, MAP(_rowsE, seq, LAMBDA(x, y, SUMIF(ISBETWEEN(ROW(range), x, CHOOSEROWS(_rowsE, y+1), TRUE, FALSE), TRUE, range))), 
      data, HSTACK(TOCOL(_rows, 1), total), 
      IFNA(VLOOKUP(ROW(range)+1, data, 2, FALSE))
   )
)

This formula will place the subtotals in the bottom row of each section separated by blank cells.

If you want the subtotals next to each blank cell, simply remove +1 within the VLOOKUP function.

Subtotals calculated up to the first blank cell and displayed adjacent to the blank cells in Google Sheets

How Do I Adapt This Formula to My Data Range?

The formula is designed for the range B2:B. To adapt it to your data range, update the following references:

  • For example, if your data range is E5:E, insert the formula in F5, assuming F5:F is empty.
  • Replace all occurrences of B2:B with your data range (e.g., E5:E).
  • Replace B2 with the first cell in your range (e.g., E5).

Formula Explanation

_rows:

IF(INDIRECT("B2:B"&XLOOKUP(TRUE, range<>"", ROW(range), ,0, -1)+1)="", ROW(range),)

This part identifies the row numbers corresponding to blank cells in the column to subtotal. It also includes the row number below the last non-empty cell to ensure the formula covers all data groups.

_rowsE:

{ROW(B2); TOCOL(_rows, 1)}

This step removes empty cells from _rows and adds the row number of the first cell in the range to the top of the array.

seq:

SEQUENCE(ROWS(_rowsE))

Generates sequence numbers corresponding to _rowsE.

total:

MAP(_rowsE, seq, LAMBDA(x, y, SUMIF(ISBETWEEN(ROW(range), x, CHOOSEROWS(_rowsE, y+1), TRUE, FALSE), TRUE, range)))

Uses the MAP function to iterate through _rowsE (row numbers of blank cells and the first row) and seq (sequence numbers). The lambda function sums the values in the range between each pair of rows.

data:

HSTACK(TOCOL(_rows,1), total)

Creates a two-column array where the first column contains row numbers corresponding to blank cells and the second column contains the corresponding subtotals.

Final Formula Expression:

IFNA(VLOOKUP(ROW(range)+1, data, 2, FALSE))

Uses VLOOKUP to find the subtotal for each row in the range and display it in the appropriate position.

Sample Sheet

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

3 COMMENTS

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.