How to Sum Values Based on a Related Cell Above in Excel
Question details
The user needs to sum numerical values in a column only if the immediately preceding cell (the cell directly above the number) matches a specific text criterion, such as the word 'Booked'.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating conditional sums in a single column where the criteria (text status) and the values (numbers) are stacked vertically or alternate in rows.
- Observed behavior
- To correctly calculate the total, the formula must evaluate the status in the cell offset by one row above each corresponding numerical value.
Ensure your data ranges are properly aligned. The criteria range must be offset by exactly one row above the sum range, and both ranges must contain the exact same number of cells.
Use the SUMIFS Function with Offset Ranges
Apply the SUMIFS formula by shifting the criteria range one row up from the sum range to check the cell immediately above.
By intentionally offsetting the ranges in the SUMIFS function, Excel evaluates the condition in one row and sums the corresponding value in the row directly below it.
Click on an empty cell where you want the final calculated total to appear.
Type the formula =SUMIFS(G2:G100, G1:G99, "Booked"). In this formula, G2:G100 is the range containing the values to sum, and G1:G99 is the offset range checking for the word 'Booked'.
If you want to easily change the criteria without editing the formula, type 'Booked' in a separate cell like K1, and update your formula to =SUMIFS(G2:G100, G1:G99, K1).
Press the Enter key on your keyboard to instantly display the sum of all values that have 'Booked' in the cell above them.
Reorganize and Convert Data to an Excel Table
Restructure your data into a formal table with distinct columns to simplify formulas and allow automatic expansion.
Easily Calculate Conditional Sums with WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools and full support for advanced functions like SUMIFS. You can calculate conditional totals quickly, reorganize your datasets into formal tables with ease, and manage large files without lag.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the stacked data.
- 2. Select the result cell: Click on the cell where you want to output the total sum.
- 3. Insert the SUMIFS formula: Navigate to the Formulas tab and click 'Insert Function', or simply type =SUMIFS(G2:G100, G1:G99, "Booked") directly into the formula bar.
- 4. View the result: Press Enter to instantly execute the conditional calculation.
Frequently Asked Questions
Why does my SUMIFS formula return a #VALUE! error?
This error typically occurs if the sum_range and the criteria_range do not contain the same number of cells. For example, if your sum range is G2:G100 (99 cells), your criteria range must also span exactly 99 cells, such as G1:G99.
Can I sum values based on multiple statuses in the related cell?
Yes. You can sum multiple statuses by wrapping the SUMIFS formula in a SUM function and using an array constant for the criteria. For example: =SUM(SUMIFS(G2:G100, G1:G99, {"Booked","Pending"})).
Is it possible to reference the related cell below instead of above?
Absolutely. If the text status is located immediately below the number, simply reverse the offset. Make G1:G99 your sum_range and G2:G100 your criteria_range.
Does this formula automatically update if I add new rows of data?
If you are using static ranges (like G2:G100), the formula will not automatically include new rows beyond row 100. To ensure new rows are included, either use dynamic named ranges, whole column references (like G:G, though less efficient), or convert the layout to a formal Excel Table with separate columns.
