Function Problems

Sum Values Adjacent to Matching Text Across a Row in Excel

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

Sum amounts next to cells containing a specific method name across multiple monthly columns.

Sum Values Adjacent to Matching Text Across a Row in Excel
Product
Microsoft Excel or WPS Spreadsheets
Device & OS
not provided
Scenario
A worksheet has method names and adjacent amounts across a row, and the user wants the total for a method such as FM Pro Rata.
Observed behavior
The desired result is a total of all adjacent amounts whose corresponding method cell matches FM Pro Rata.
Before you start

Identify the row containing the method names and the cells immediately to their right containing the amounts. In this example, the method range is F98:Z98 and the corresponding amount range is G98:AA98.

Solution 1Recommended

Use SUMIF to Add Amounts Next to a Matching Method

Use this formula when each amount is immediately to the right of its method name and you need to total all matches across the row.

The two ranges must have the same dimensions and be offset by one column. SUMIF checks each cell in the first range and adds the cell in the corresponding position of the second range when the method matches.

1
Select an empty result cell

Click an empty cell outside F98:AA98 where you want the total to appear.

2
Enter the SUMIF formula

Type =SUMIF(F98:Z98,"FM Pro Rata",G98:AA98) in the selected cell and press Enter.

3
Check the result

Review the method cells in F98:Z98 and their adjacent amounts in G98:AA98. The result includes the amount next to every cell that matches FM Pro Rata.

Use SUMIF to Add Amounts Next to a Matching Method
Use a cell for the method name: To make the criterion editable, enter the method name in a cell such as A1 and use =SUMIF(F98:Z98,A1,G98:AA98).
WPS Spreadsheets

Sum Matching Monthly Amounts in WPS Spreadsheets

Open your worksheet in WPS Spreadsheets and use SUMIF to add amounts next to matching method names across a row.

  1. 1. Open the worksheet: Launch WPS Spreadsheets and use Open to select your workbook.
  2. 2. Select a result cell: Click an empty cell outside the method and amount ranges.
  3. 3. Enter the formula: Type =SUMIF(F98:Z98,"FM Pro Rata",G98:AA98), adjusting the ranges and method name to match your worksheet, and press Enter.
  4. 4. Save the workbook: Use File > Save to keep the formula in your workbook.
Total multiple matching amounts with a single SUMIF formulaUse a cell reference to change the method without editing the formulaOpen and edit Microsoft Excel XLSX filesWork with familiar spreadsheet formulas and menus
microsoft office alternative - wps office

Frequently Asked Questions

Does SUMIF add amounts for every matching method?

Yes. In =SUMIF(F98:Z98,"FM Pro Rata",G98:AA98), every matching cell in F98:Z98 contributes its corresponding adjacent amount to the total.

Can I put the method name in a separate cell?

Yes. Enter the method name in A1, then use =SUMIF(F98:Z98,A1,G98:AA98). Changing A1 changes the criterion.

Why do the method and amount ranges start in different columns?

Each amount is one column to the right of its method, so the sum range starts one column after the criteria range. Both ranges must have the same number of cells.

How can I sum amounts using more than one condition?

Use SUMIFS with the amount range first, followed by each criteria range and criterion. Make sure each criteria range has the same dimensions as the amount range.