How to Use Excel SUMIF to Sum Populations for a List of Countries
Question details
The user needs to calculate the combined population for a specific list of European countries using an Excel formula.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating a total sum based on a list of multiple criteria (country names) located in a separate column.
- Observed behavior
- The user requires a formula that matches individual country names from a criteria list against a main dataset and sums the corresponding values.
Ensure that the spelling of the country names in your criteria list perfectly matches the names in your main dataset, and check that there are no hidden trailing spaces in the cells.
Use SUM and SUMIF Functions Together
Combine the SUM and SUMIF functions to evaluate an entire range of criteria simultaneously and calculate the aggregated total.
By nesting a SUMIF function inside a SUM function, you can pass an array of criteria (like your list of specific countries) instead of a single condition. The SUMIF function will calculate the total for each country individually, and the SUM function will add those totals together.
Click on a blank cell where you want the final combined population total to be displayed (for example, cell D1).
Type the following formula into the formula bar: =SUM(SUMIF(A1:A195,C1:C195,B1:B195)). In this formula, A1:A195 is the range containing all country names, C1:C195 is your specific list of European countries to check against, and B1:B195 contains the populations to be summed.
Press Enter to execute the formula. The cell will now display the combined population for all the countries listed in column C.
Calculate Complex Data Quickly with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced data analysis and complex array formulas, making it easy to calculate sums across multiple criteria without hassle.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your country and population data.
- 2. Select a Target Cell: Click the cell where you want the final calculation to appear.
- 3. Enter the Formula: Input the =SUM(SUMIF(A1:A195,C1:C195,B1:B195)) formula and press Enter to instantly view your aggregated data.
Frequently Asked Questions
Why is my SUM(SUMIF()) formula returning an error or just the first value?
In non-dynamic array versions of spreadsheet software, you must press Ctrl+Shift+Enter instead of just Enter. This tells the program to evaluate the formula across the entire array of criteria.
What happens if my criteria list contains duplicate country names?
If your criteria list (Column C) contains duplicate names, the SUMIF function will evaluate them twice, resulting in the population for that country being double-counted. Always ensure your criteria list contains only unique values.
Can I use this formula to sum data based on partial text matches?
Yes, you can use wildcard characters like an asterisk (*) in your criteria list. For example, typing 'North*' in your criteria cell will match any entry starting with the word North.
