How to Use SUMIFS Between Two Numbers with Multiple Criteria in Excel
Question details
The user needs to calculate a sum using the SUMIFS function based on multiple criteria, including a condition where values in a specific range must fall between two numbers (e.g., greater than 0 and less than 35).
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Setting up a conditional sum formula where one of the criteria requires evaluating a numeric range with upper and lower limits.
- Observed behavior
- The SUMIFS formula incorrectly returns zero, which occurs when criteria string syntax is incorrect, criteria range dimensions are mismatched, or numeric data is formatted as text.
Verify that all criteria ranges in your formula span the exact same number of rows and ensure that the numbers in your data columns are formatted as numbers rather than text.
Apply Correct Syntax for Greater Than and Less Than Criteria
Fix formula errors by formatting the upper and lower bounds as separate criteria strings with double quotation marks.
When calculating between two numbers, the SUMIFS function requires you to state the criteria range twice: once for the lower limit and once for the upper limit.
Click on the cell where you want the total sum to be displayed.
In your SUMIFS formula, select your criteria range (e.g., HX!EY:EY) and enter the lower bound enclosed in double quotes, such as ">0".
Add a comma, select the exact same criteria range again (HX!EY:EY), and enter the upper bound enclosed in double quotes, such as "<35".
Press Enter. If you want the calculation to include the limits themselves, adjust the criteria to ">=0" and "<=35".
Convert Text-Formatted Numbers and Align Range Dimensions
Resolve issues where SUMIFS returns zero despite correct syntax by ensuring data types and range sizes match.
Master Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides an intuitive formula builder and automatic error-checking features. You can effortlessly calculate conditional sums between number ranges without running into frustrating syntax headaches or zero-value errors.
- 1. Open your file: Launch WPS Office and open your spreadsheet document.
- 2. Access the function builder: Click on the 'Formulas' tab at the top and select 'Insert Function' to search for SUMIFS.
- 3. Input the arguments: Use the Function Arguments dialog box to input your sum ranges and separate criteria like ">0" and "<35" step-by-step.
- 4. Apply the calculation: Click 'OK'. WPS Spreadsheet automatically adds the necessary syntax formatting to output the accurate sum.
Frequently Asked Questions
Why does my SUMIFS formula return zero when the data is clearly there?
This usually happens if the numbers in your criteria range are accidentally stored as text, if the criteria ranges have different row dimensions (e.g., referencing A1:A10 and B1:B15), or if the criteria syntax is missing double quotation marks around operators like ">0".
How do I include the exact boundary numbers in my SUMIFS between criteria?
To include the exact upper and lower limit numbers, you need to add an equals sign to your operators inside the double quotation marks. For example, use ">=0" instead of ">0", and "<=35" instead of "<35".
Can I reference another cell for the greater than or less than criteria in SUMIFS?
Yes. If your limit number is stored in a cell (like A1), you must enclose the logical operator in quotation marks and use an ampersand (&) to join it with the cell reference. The correct syntax for the criteria argument would look like ">"&A1.
