'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use SUMIFS for Multiple Criteria in an Excel Table
Question details
The user needs to calculate separate totals for different bills and a grand total using multiple criteria (month, name, and category), but the results fail to update when dropdown values are changed.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating dynamic conditional sums in an Excel worksheet based on multiple dropdown criteria selections.
- Observed behavior
- The SUMIFS formula either produces incorrect results or the totals do not refresh automatically when new criteria values are selected from the dropdowns.
Ensure that your month criteria are formatted as real dates rather than text, and verify that the dropdown list values exactly match the spelling in your source data.
Construct the SUMIFS Formula with Date Ranges
Use SUMIFS combined with the EOMONTH function to calculate totals accurately based on specific date ranges, names, and categories.
To sum values over a specific month chosen via a dropdown, you must define the start and end of that month in the criteria. The EOMONTH function helps calculate these boundaries automatically.
Select the cell for your first total (e.g., D2) and enter the following formula: =SUMIFS('Sheet A'!C2:C1000, 'Sheet A'!$A2:$A1000, ">"&EOMONTH($A2,-1), 'Sheet A'!$A2:$A1000, "<="&EOMONTH($A2,0), 'Sheet A'!$B2:$B1000, $B2, 'Sheet A'!$F2:$F1000, $C2)
Click and drag the fill handle from the bottom-right corner of cell D2 across the adjacent cells to apply the formula to the other bill columns.
In the grand total cell (e.g., G2), type the formula =SUM(D2:F2) and press Enter to sum the calculated individual bills.
Enable Automatic Calculation
If changing the dropdown values does not update the results, the workbook calculation mode might be set to manual.
Verify Data Formatting and Table References
Check common data entry and formatting errors that prevent the SUMIFS function from matching criteria properly.
Easily Calculate Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced logical functions like SUMIFS, making it easy to filter and sum data across multiple criteria. It handles Excel tables, dropdown lists, and dynamic calculations seamlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data tables and criteria sheets.
- 2. Insert the SUMIFS formula: Select the target cell, type =SUMIFS(, and use the intuitive formula helper to select your sum ranges and condition ranges without manually typing.
- 3. Set up dynamic dropdowns: Navigate to Data > Data Validation to quickly create and manage dropdown lists for your criteria.
- 4. Ensure automatic calculation: Go to the Formulas tab and click Calculation Options to verify that 'Automatic' is selected for instant updates.
Frequently Asked Questions
Why does my SUMIFS formula return a #VALUE! error?
This typically occurs if the sum_range and the criteria_range arguments are not the same size. Ensure that all the ranges in your formula (for example, C2:C1000 and A2:A1000) cover the exact same number of rows and columns.
Can I use wildcard characters with SUMIFS in a dropdown list?
Yes, you can use wildcards like the asterisk (*) or question mark (?) within your criteria cells to match partial text strings, and the SUMIFS formula will interpret them correctly to calculate the sum.
How do I sum values based on a month without using the EOMONTH function?
You can create a helper column in your source data that extracts the month number using the MONTH() function. Then, you can simply use that helper column as your criteria range in the SUMIFS formula, pointing it to a month number criteria.
