How to Use SUMIFS in WPS Spreadsheets to Sum Bananas from Ecuador and India
Question details
The user needs to calculate total pounds of bananas produced in 2021 by Ecuador and India using SUMIFS in WPS Spreadsheets, applying both product and country criteria.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a produce data range with columns for product, country, and pounds and wants a formula that sums banana pounds where the country is either India or Ecuador.
- Observed behavior
- The formula must return 532,108 pounds by combining SUMIFS results for both India and Ecuador.
Open the WPS Spreadsheets workbook and confirm that the data range C2:C11 contains the pound values, A2:A11 contains product names, and B2:B11 contains country names. Ensure the criteria values use the exact text 'Bananas', 'India', and 'Ecuador'.
Use SUM with SUMIFS and an Array Constant for Ecuador and India
Use this when you need to sum values that match one product and any of multiple countries in WPS Spreadsheets.
SUMIFS normally accepts one criterion per criteria range. To include two countries, wrap SUMIFS inside SUM and supply the country criteria as an array constant {"India","Ecuador"}. WPS Spreadsheets evaluates SUMIFS for each country and SUM adds the results.
Click the cell where you want the total pounds of bananas to appear.
Type =SUM(SUMIFS(C2:C11,A2:A11,"Bananas",B2:B11,{"India","Ecuador"})).
Press Enter. The formula returns 532,108 pounds by adding the India and Ecuador SUMIFS results.
Add Two Separate SUMIFS Formulas for India and Ecuador
Use this when you prefer to avoid array constants and want the country criteria stored or read separately.
Create Multi-Criteria SUMIFS Totals in WPS Spreadsheets
Use WPS Spreadsheets to combine SUMIFS with SUM and array constants, so you can total values such as bananas from Ecuador and India without separate helper formulas.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the produce data in columns A, B, and C.
- 2. Select the target cell: Click an empty cell where the total should appear.
- 3. Enter the formula: Type =SUM(SUMIFS(C2:C11,A2:A11,"Bananas",B2:B11,{"India","Ecuador"})) and press Enter.
- 4. Check the result: Confirm that the cell displays 532,108 pounds, which is the combined total for India and Ecuador.
Frequently Asked Questions
Why does a standard SUMIFS formula not accept India and Ecuador in one country range?
A single SUMIFS criteria pair accepts one criterion unless you use an array constant. In WPS Spreadsheets, wrap SUMIFS in SUM and use {"India","Ecuador"} as the criteria, or add two separate SUMIFS formulas.
Do I need to press Ctrl+Shift+Enter for the array constant in WPS Spreadsheets?
No. The formula =SUM(SUMIFS(C2:C11,A2:A11,"Bananas",B2:B11,{"India","Ecuador"})) returns the total normally when entered with Enter in WPS Spreadsheets.
Can I use cell references instead of typing India and Ecuador in the formula?
Yes. For cell-based multiple criteria, use separate SUMIFS formulas added together, such as =SUMIFS(C2:C11,A2:A11,"Bananas",B2:B11,G1)+SUMIFS(C2:C11,A2:A11,"Bananas",B2:B11,G2), where G1 and G2 contain the country names.
What should I check if the formula returns an error or an unexpected total?
Make sure the criteria ranges have the same size, the text matches exactly, and the pound values are numeric. Check for extra spaces or capitalization differences in 'Bananas', 'India', and 'Ecuador'.
