Function Problems

How to Use Excel SUMIFS to Add Values in Numeric Ranges

Huda Qurayshi Oct 22, 2026 868 views

Question details

The user needs to calculate the total sum of amounts in a specific column based on defined numeric brackets in another column.

How to Use Excel SUMIFS for Adding Values in Numeric Ranges

Product: Excel
Device & OS: not provided
Scenario: Calculating total values across grouped numeric brackets (e.g., 1-30, 31-60) for financial data analysis or reporting purposes.
Observed behavior: The goal is to accurately sum amounts in range B2:B401 where the corresponding values in A2:A401 fall between defined upper and lower limits without overlapping calculations.

Before you start

Ensure that the criteria range (A2:A401) and the sum range (B2:B401) contain the exact same number of rows to avoid encountering a #VALUE! error.

Solution 1Recommended

Use SUMIFS with Greater Than and Less Than Operators

Apply the SUMIFS function using logical comparison operators to properly define the lower and upper bounds of your numeric brackets.

The SUMIFS function adds cells specified by a given set of conditions or criteria. By using ">0" and "<=30", you can define the first specific bracket. For the subsequent bracket, it is crucial to start at ">=31" to ensure no values overlap and cause double-counting.

Select the target cell

Click on the blank cell where you want the total for the 1-30 numeric range to appear.

Enter the formula for the first range

Type =SUMIFS($B$2:$B$401,$A$2:$A$401,">0",$A$2:$A$401,"<=30") and press the Enter key.

Enter the formula for the second range

Select the next cell for your 31-60 range, type =SUMIFS($B$2:$B$401,$A$2:$A$401,">=31",$A$2:$A$401,"<=60") and press Enter.

Use SUMIFS with Greater Than and Less Than Operators

Use Absolute References: Adding dollar signs ($) locks the cell references, ensuring the selected range remains accurate even if you drag or copy the formula to other cells.

Advanced Data Analysis

Easily Calculate Complex Data Ranges with WPS Spreadsheet

WPS Spreadsheet fully supports the SUMIFS function, allowing you to seamlessly calculate totals across dynamic numeric ranges. Enjoy a highly familiar and intuitive interface designed to boost your daily productivity.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data ranges.
2. Input the SUMIFS formula: Select your result cell and enter the SUMIFS formula to define your specific numeric bounds.
3. Press Enter to calculate: Hit Enter to instantly view the calculated sum for your specified brackets.

100% compatible with Microsoft Excel formulas and file formatsBuilt-in formula suggestions to prevent syntax errors with SUMIFS and other functionsLightweight application that runs smoothly on any deviceFree to use with comprehensive data analysis and reporting tools

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIFS formula returning a #VALUE! error?

A #VALUE! error usually occurs if the sum_range (e.g., B2:B401) and the criteria_range (e.g., A2:A401) are not the exact same size. Verify that both ranges cover the same number of rows and columns.

Can I use cell references for the numeric limits instead of hardcoding them?

Yes. Instead of typing ">0" directly in the formula, you can use a cell reference by concatenating the operator and the cell. For example, use ">"&D1 where cell D1 contains the number 0.

How do I add a third condition to the SUMIFS formula?

You can simply append another criteria_range and criteria pair at the end of your existing formula. For example: =SUMIFS($B$2:$B$401, $A$2:$A$401, ">0", $A$2:$A$401, "<=30", $C$2:$C$401, "Completed").

Excel & Data Problems