Function Problems

How to Sum Two Columns Between Blank Rows in Excel

Camila MilosovichCamila Milosovich Oct 16, 2026 869 views

Question details

The user needs a formula to sum values from two distinct columns based on changing group identifiers or blank rows that separate the data.

How to Sum Two Columns Between Blank Rows Using a Formula
Product
Excel / WPS Spreadsheet
Device & OS
not provided
Scenario
Calculating subtotals for grouped data where groups are separated by blank rows or distinct identifiers in a specific column.
Observed behavior
The goal is to display a combined total for the two columns only at the beginning or end of each data group, skipping duplicate totals in between.
Before you start

Ensure your dataset ranges are properly defined. Identify the column letter containing your group identifiers (e.g., Column AO) and the two target columns you wish to sum (e.g., Columns AQ and AR).

Solution 1Recommended

Use a Combined IF and SUMIF Formula

Use a logical formula to check for group changes and conditionally sum the matching rows across two columns.

This method evaluates whether the current row's identifier differs from the previous row. If it does, it calculates the sum of two target columns for that specific identifier. If the identifier is identical to the one above it, the formula leaves the cell blank to avoid duplicate totals.

1
Select the target cell

Click on the first cell in your designated totals column where you want the combined sum to appear.

2
Enter the formula

Input the following formula: =IF(AO2<>AO1,SUMIF($AO$2:$AO$16,AO2,$AQ$2:$AQ$16)+SUMIF($AO$2:$AO$16,AO2,$AR$2:$AR$16),""). Replace AO with your identifier column, and AQ and AR with the columns you wish to sum.

3
Apply the calculation

Press Enter to execute the formula.

4
Fill the formula down

Click and drag the fill handle (the small square at the bottom-right of the active cell) down to copy the formula to the rest of the rows in your dataset.

Use a Combined IF and SUMIF Formula
Absolute References: Make sure to use absolute references (the $ signs) for your data ranges (e.g., $AO$2:$AO$16) so they do not shift when you drag the formula down to other rows.

Effortlessly Manage Data and Formulas with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced formulas like IF and SUMIF, allowing you to easily manage grouped data and complex calculations. It offers a highly familiar, user-friendly interface that lets you process large datasets without lag.

  1. 1. Open your file: Launch WPS Spreadsheet and open the document containing your grouped data.
  2. 2. Select the totals cell: Click on the cell where you want the first subtotal to appear.
  3. 3. Input the formula: Type the formula =IF(AO2<>AO1,SUMIF($AO$2:$AO$16,AO2,$AQ$2:$AQ$16)+SUMIF($AO$2:$AO$16,AO2,$AR$2:$AR$16),"") and hit Enter.
  4. 4. Drag to fill: Use the fill handle to drag the formula down the column, instantly calculating subtotals for all groups.
Fully supports advanced Excel functions, including SUMIF, IF, and complex arrays.Seamlessly opens, edits, and saves Microsoft Excel (.xlsx) files with perfect format compatibility.Completely free to download and use, offering a lightweight alternative for daily office tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Can I sum more than two columns using this method?

Yes, you can append additional SUMIF functions to the formula for each extra column you want to include. For example, simply add +SUMIF($AO$2:$AO$16,AO2,$AS$2:$AS$16) to the end of the existing formula before the final quotation marks.

Why is my formula returning a zero or an error instead of the correct sum?

This typically happens if the ranges in the SUMIF function do not perfectly align. Ensure that the criteria range (e.g., $AO$2:$AO$16) and the sum ranges (e.g., $AQ$2:$AQ$16) cover the exact same number of rows and start at the same row number.

What if my groups are separated by completely blank rows instead of changing text identifiers?

If you rely solely on blank rows, you can alter the IF logic to check if the cell above is blank using the ISBLANK function. For example, =IF(ISBLANK(A1), SUMIF(...), ""). Alternatively, the Subtotal tool found in the Data tab can automate this process without complex formulas.