Sum Values Through a Matching Row in Excel
Question details
Find a value in column G and sum the corresponding values in column C from a starting row through the matching row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A lookup value in L1 identifies the ending row, and the sum starts at C3.
- Observed behavior
- The desired result includes every value in column C from C3 through the row where the first matching value appears in column G.
Place the lookup value in L1 and confirm that the values to sum are in column C and the matching values are in column G. Check that the sum and lookup ranges cover the same rows.
Use SUM, INDEX, and MATCH to sum through the matching row
Use this formula when the search and sum both start on row 3 and end within row 46.
MATCH finds the first exact match in G3:G46. INDEX returns the corresponding cell in C3:C46, which becomes the endpoint of the SUM range.
Type the value to find in cell L1.
Select an empty result cell, such as J5, and enter =SUM(C3:INDEX(C3:C46,MATCH(L1,G3:G46,0))).
Press Enter. The result includes the value in column C on the matching row.

Sum through a matching row in WPS Spreadsheet
Use the same SUM, INDEX, and MATCH formula in WPS Spreadsheet to find a value in column G and total column C through the corresponding row.
- 1. Open the spreadsheet: Open WPS Spreadsheet and select File > Open to choose your workbook.
- 2. Set the lookup value: Enter the value to find in cell L1, and check that the matching values are in G3:G46.
- 3. Add the formula: Select an empty cell and enter =SUM(C3:INDEX(C3:C46,MATCH(L1,G3:G46,0))).
- 4. Calculate and save: Press Enter to see the total, then use File > Save to save your changes.

Frequently Asked Questions
Does the formula include the matching row?
Yes. SUM includes the cell returned by INDEX, so the value in column C on the matching row is part of the total.
What happens if the lookup value appears more than once?
MATCH(L1,G3:G46,0) finds the first exact match in the range, so the sum stops at that row.
How do I change the starting or ending row?
Edit both ranges together in the formula. For example, use C5:C100 and G5:G100, and change the starting reference from C3 to C5.
Why does the formula return #N/A?
MATCH returns #N/A if it cannot find an exact match for L1 in G3:G46. Check the lookup value and range, or wrap the formula in IFERROR to display a message.




