Correct a SUMIF Formula with a Dynamic Range in Excel
Question details
Create a SUMIF formula whose ranges end at a row found by MATCH.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sum values in column B when the corresponding cells in column E equal T90, stopping at the row where column A first contains ABC.
- Observed behavior
- Concatenating range text with a MATCH result produces text rather than a usable range reference, and mismatched range dimensions can produce unintended results.
Check that column A contains ABC, column E contains the T90 criteria, and column B contains the values to sum. The examples start at row 1 and include the first row where ABC is found.
Use INDEX and MATCH to create matching dynamic ranges
Use this approach to build dynamic references without the volatility of INDIRECT.
In Excel, click the cell where you want the conditional sum to appear.
Type =SUMIF(E1:INDEX(E:E,MATCH("ABC",A:A,0)),"T90",B1:INDEX(B:B,MATCH("ABC",A:A,0))) and press Enter.
Verify that the formula sums column B only for rows where column E equals T90, through the first row containing ABC in column A.

Use INDIRECT to turn dynamic range text into references
Use INDIRECT when you need to construct the range addresses as text.
Build a Dynamic SUMIF Range in WPS Spreadsheets
Use INDEX and MATCH in WPS Spreadsheets to create aligned dynamic ranges for a conditional sum.
- 1. Open the workbook: Open WPS Spreadsheets and select File > Open to choose your workbook.
- 2. Select a result cell: Click the cell where you want to display the sum.
- 3. Enter the formula: Type =SUMIF(E1:INDEX(E:E,MATCH("ABC",A:A,0)),"T90",B1:INDEX(B:B,MATCH("ABC",A:A,0))) and press Enter.
- 4. Save the workbook: Choose File > Save or Save As and select an Excel-compatible format if needed.

Frequently Asked Questions
Why does concatenating a range with MATCH not work directly in SUMIF?
Concatenation creates a text string, not a range reference. Use INDEX to build a reference or INDIRECT to convert a valid address string into one.
Do the SUMIF criteria and sum ranges need to be the same size?
Keep them the same size and aligned by row. For example, pair E1:E12 with B1:B12 so each criterion is evaluated against the intended sum cell.
What happens if ABC appears more than once in column A?
MATCH("ABC",A:A,0) returns the position of the first exact match, so the example ranges end at that row.
Why does the formula show #N/A?
MATCH returns #N/A when it cannot find ABC in column A. Check the lookup value and column, including any extra spaces.




