Formula Errors

How to Use SUMIFS to Flag Cumulative Excel Balances by Two Criteria

Camila Milosovich Sep 27, 2026 868 views

Question details

The user needs to create a formula that marks a row with an 'X' when a running cumulative balance drops below a specified threshold, grouped by two distinct criteria.

How to Use SUMIFS to Flag Cumulative Excel Balances by Two Criteria

Product: Excel
Device & OS: not provided
Scenario: Tracking financial transactions and automatically flagging rows when their cumulative total breaches a predefined amount based on multiple grouping conditions like Dealer Number and CMP Account.
Observed behavior: The goal is to accurately calculate a running total conditionally based on two separate columns and return a specific text flag ('X') when the threshold is met, handling any potential formula errors gracefully.

Before you start

Ensure your data is sorted correctly by date or transaction order so the chronological running totals calculate as expected, and verify that your criteria columns contain clean text without hidden trailing spaces.

Solution 1Recommended

Use SUMIFS with Expanding Ranges for Two Criteria

Use the SUMIFS function coupled with an expanding range reference to calculate a running total based on two separate criteria, and wrap it in an IF statement to generate the flag.

The SUMIFS function calculates sums based on multiple criteria, which is required when grouping by more than one column. By locking the first cell reference using absolute references (e.g., $G$11:G11), you create an 'expanding range' that grows as you copy the formula down the column. This technique is essential for calculating a running cumulative total.

Select the target cell

Click on the first cell in the column where you want the 'X' flag to appear (for example, Row 11).

Build the SUMIFS function

Enter the core SUMIFS formula to calculate the running total: SUMIFS($G$11:G11, $E$11:E11, E11, $H$11:H11, H11). In this formula, column G holds the values to sum, E is the first criterion range (e.g., Dealer Number), and H is the second criterion range (e.g., CMP Account).

Add the IF logic

Wrap the SUMIFS function in an IF statement to check the cumulative total against your restricted amount threshold: IF(SUMIFS($G$11:G11, $E$11:E11, E11, $H$11:H11, H11) > N11-I11, "X", "").

Wrap with IFERROR

Add an IFERROR function to handle any potential calculation errors gracefully and output a blank space instead of an error code: =IFERROR(IF(SUMIFS($G$11:G11, $E$11:E11, E11, $H$11:H11, H11) > N11-I11, "X", ""), " ").

Apply to the entire column

Press Enter to save the formula. Then, click and drag the fill handle at the bottom-right corner of the cell down to apply this expanding formula to all relevant rows in your dataset.

Use SUMIFS with Expanding Ranges for Two Criteria

Expanding Range Setup: Using absolute row references for the start of the range (like $G$11) and relative references for the end of the range (like G11) is the critical secret to making the cumulative sum work accurately across rows.

Solution 2

Use SUMIF for a Single Grouping Criterion

If your grouping requirements change and you only need to evaluate a single criterion, use the simpler SUMIF function instead.

Master Complex Logical Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced mathematical and logical functions like SUMIFS, SUMIF, IF, and IFERROR. You can easily calculate cumulative balances, set up conditional data flags, and manage large financial datasets with perfect formula compatibility.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your transaction records.
2. Select the target cell: Click on the cell where you want the cumulative balance flag to be displayed.
3. Input the formula: Type your =IFERROR(IF(SUMIFS(...))) formula, ensuring you use the F4 key to quickly apply absolute references ($) for your expanding ranges.
4. Fill down the data: Double-click the fill handle on the bottom right of the cell to instantly apply the formula to the entire column.

Fully compatible with Microsoft Excel formulas including SUMIFS and expanding ranges.Intuitive formula builder with real-time syntax highlighting to prevent calculation errors.Fast processing engine for handling large datasets with complex running total arrays.Completely free to use with a lightweight installation.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the standard SUMIF function work for this scenario?

The SUMIF function is restricted to evaluating only one condition or criterion range. Because this scenario requires grouping transactions by two distinct criteria (Dealer Number and CMP Account), the multi-criteria SUMIFS function is necessary.

What is an expanding range in spreadsheet formulas?

An expanding range uses a combination of absolute and relative cell references, such as $G$11:G11. As you copy the formula down the rows, the starting point remains permanently fixed at row 11, but the endpoint changes dynamically (e.g., $G$11:G12, $G$11:G13). This technique is how spreadsheets calculate running or cumulative totals.

How do I hide #VALUE! or other errors if the formula fails on blank rows?

Wrapping your main formula inside an IFERROR() function is the best practice. By writing =IFERROR([your_formula], " "), any calculation error will automatically be replaced with a blank space or your preferred alternative text, keeping your spreadsheet clean.

Excel & Data Problems