Function Problems

Sum Values Through a Matching Row in Excel

Anum MushtaqAnum Mushtaq Nov 18, 2026 869 views

Question details

Find a value in column G and sum the corresponding values in column C from a starting row through the matching row.

Sum Values Through a Matching Row in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the lookup value

Type the value to find in cell L1.

2
Enter the formula

Select an empty result cell, such as J5, and enter =SUM(C3:INDEX(C3:C46,MATCH(L1,G3:G46,0))).

3
Check the result

Press Enter. The result includes the value in column C on the matching row.

Use SUM, INDEX, and MATCH to sum through the matching row
Handle a missing match: To display a message instead of an error when no match is found, enter =IFERROR(SUM(C3:INDEX(C3:C46,MATCH(L1,G3:G46,0))),"Not found"). IFERROR also hides other errors in the formula.
Work with formulas in WPS Spreadsheet

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. 1. Open the spreadsheet: Open WPS Spreadsheet and select File > Open to choose your workbook.
  2. 2. Set the lookup value: Enter the value to find in cell L1, and check that the matching values are in G3:G46.
  3. 3. Add the formula: Select an empty cell and enter =SUM(C3:INDEX(C3:C46,MATCH(L1,G3:G46,0))).
  4. 4. Calculate and save: Press Enter to see the total, then use File > Save to save your changes.
Sum values through the first exact match with familiar spreadsheet functions.Open and save Microsoft Excel XLSX and XLS files.Edit formulas in a familiar spreadsheet interface.Use WPS Office as a free, lightweight way to work with spreadsheets.
microsoft office alternative - wps office

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.