How to Sum Two Columns Between Blank Rows in Excel
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.

- 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.
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).
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.
Click on the first cell in your designated totals column where you want the combined sum to appear.
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.
Press Enter to execute the formula.
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 the Built-in Subtotal Feature
Automatically insert subtotals for grouped data without manually typing complex formulas.
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. Open your file: Launch WPS Spreadsheet and open the document containing your grouped data.
- 2. Select the totals cell: Click on the cell where you want the first subtotal to appear.
- 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. Drag to fill: Use the fill handle to drag the formula down the column, instantly calculating subtotals for all groups.

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.




