Function Problems

How to Sum All Matching Rows by City in WPS Office Spreadsheets

Muhammad Talha Nov 13, 2026 869 views

Question details

The user needs to calculate the total score for every row that contains the same city instead of only returning the first matching value with VLOOKUP.

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: The user has a dataset with city names and total scores, and wants to add all scores that share a city.
Observed behavior: VLOOKUP only finds and returns the first matching city row, so it cannot sum all matching rows.

Before you start

Before entering the formula, make sure the city names are consistent—check for extra spaces or case differences—and identify the exact ranges for the city column and the score column.

Solution 1Recommended

Use SUMIF to Total Scores for One City

Use this when you only need to sum values based on a single city criterion.

SUMIF scans the city range and adds every score in the total score range where the city equals the criterion. Unlike VLOOKUP, it does not stop at the first match.

Select the result cell

Click the cell where you want the city total to appear.

Enter the SUMIF formula

Type =SUMIF(CityRange,A2,TotalScoreRange), replacing CityRange with your city column and TotalScoreRange with the score column.

Press Enter and fill down

Press Enter to calculate the total, then drag the fill handle down to apply the formula to other city rows.

Result: The cell now shows the sum of every score row that matches the city in A2.

Solution 2

Use SUMIFS to Sum by City with Multiple Criteria

Use this when you need to match both city and another condition, such as status or date.

Direct solution

Calculate City Totals Directly in WPS Office Spreadsheets

Use WPS Office Spreadsheets to replace single-match lookups with SUMIF or SUMIFS formulas and get accurate city totals. It opens Excel workbooks, keeps your formulas intact, and provides a familiar spreadsheet interface.

1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook containing your city and score data.
2. Click the result cell: Select the cell where you want the city total to appear.
3. Enter the SUMIF formula: Type =SUMIF(CityRange,A2,TotalScoreRange) into the formula bar and press Enter.
4. Use SUMIFS if needed: For multiple criteria, type =SUMIFS(TotalScoreRange,CityRange,A2,StatusRange,B2) and press Enter.
5. Save the workbook: Save the file in .xlsx format to keep the formulas and results for future use.

Sums all matching rows by city with native SUMIF and SUMIFS supportOpens and saves .xlsx, .xls, and .csv files for Excel compatibilityFree, lightweight spreadsheet tool with a familiar ribbon interfaceNo need to switch from Excel formulas or rebuild workbooks

microsoft office alternative - wps office

Frequently Asked Questions

Can SUMIF sum all occurrences of a city, not just the first match?

Yes. SUMIF adds every cell in the sum range where the criteria range equals the city, rather than stopping at the first match like VLOOKUP.

What is the difference between SUMIF and SUMIFS?

SUMIF works with one condition, such as city only. SUMIFS supports multiple criteria and places the sum range first, for example =SUMIFS(TotalScoreRange,CityRange,A2,StatusRange,B2).

Does this work in both WPS Office Spreadsheets and Microsoft Excel?

Yes. The same SUMIF and SUMIFS formulas work in WPS Office Spreadsheets and Microsoft Excel, and workbooks can be opened and saved in .xlsx format.

Why does VLOOKUP not sum matching rows by city?

VLOOKUP is designed to return one value from the first matching row. To aggregate all matching rows, use SUMIF for one condition or SUMIFS for multiple conditions.

Can I sum by city when the city names have different capitalization?

SUMIF and SUMIFS are case-insensitive, so capitalization usually does not matter. However, extra spaces or inconsistent spelling can prevent matches, so clean the data first.

Excel & Data Problems