Function Problems

How to Group and Sum Unique Excel Results Between Two Values

Steve K Sep 28, 2026 870 views

Question details

The user needs to group unique item and location data and sum their corresponding values for departments that fall strictly within a specified numerical range, such as between 3000 and 4000.

How to Group and Sum Unique Excel Results Between Two Values

Product: Excel
Device & OS: not provided
Scenario: Calculating summed totals for unique categorized entries restricted by a specific upper and lower numerical boundary.
Observed behavior: Generating a dynamic grouped summary report that conditionally sums data falling exactly between two specified limits.

Before you start

Ensure your dataset is organized with clear column headers (e.g., Department, Item, Location, Value) and define the cell references that contain your lower and upper range limits.

Solution 1Recommended

Use the GROUPBY Function for Dynamic Summation

The newly introduced GROUPBY function allows you to dynamically group unique combinations and calculate conditional sums in a single formula.

This method is highly efficient for Microsoft 365 users, allowing dynamic arrays to handle both the grouping of text (like items and locations) and the conditional summing (using multiplication logic for the limit boundaries).

Set up limit cells

Enter your lower range limit in a dedicated cell (e.g., G1) and your upper range limit in another cell (e.g., G2).

Select the output cell

Click on the empty cell where you want the upper-left corner of your summarized data to appear (e.g., F4).

Enter the GROUPBY formula

Type the formula: =GROUPBY(B4:C13,D4:D13,SUM,,0,,((A4:A13>=G1)*(A4:A13<=G2))). Adjust B4:C13 to your grouping columns, D4:D13 to your values, and A4:A13 to your criteria column.

Execute the calculation

Press Enter. The formula will automatically spill the grouped items and their summed values for rows matching the criteria range.

Use the GROUPBY Function for Dynamic Summation

Version Compatibility: The GROUPBY function is only available in the latest Microsoft 365 insider builds. If you see a #NAME? error, use the PivotTable method instead.

Solution 2

Create a PivotTable with Range Filters

A PivotTable provides a visual, interface-driven way to group unique records and apply range filters without writing complex formulas.

Efficient Data Analysis with WPS Office

Group and Sum Complex Data Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful data analysis tools, including dynamic array support and advanced PivotTables, making it incredibly simple to group unique items and calculate conditional sums.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your dataset.
2. Insert a PivotTable: Select your data range, navigate to the 'Insert' tab, and click 'PivotTable'.
3. Configure grouping and values: Drag the categories you want to group into the 'Rows' section and the numbers to sum into the 'Values' section.
4. Set your range limits: Drag your numerical criteria into the 'Filter' area and utilize the filter settings to restrict the data between your two values.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Advanced PivotTable capabilities for code-free data grouping.Lightweight software with fast processing for large datasets.Free built-in data visualization and reporting tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use SUMIFS instead of GROUPBY to achieve this?

Yes. You can first extract unique combinations using the UNIQUE function. Then, in the adjacent column, write a SUMIFS formula that references the spilled unique values array alongside your upper and lower limit conditions.

Why isn't the GROUPBY function working in my Excel?

The GROUPBY function is a recent addition available primarily to Microsoft 365 Insiders. If you are on Excel 2021, 2019, or older, the function will return a #NAME? error. Use a PivotTable or a combination of UNIQUE and SUMIFS instead.

How do I filter a PivotTable dynamically using cell values?

Native PivotTables do not link directly to cell values for 'Between' filters out of the box. You must either manually adjust the 'Value Filters' in the menu or use a simple VBA macro to link the PivotTable filter to specific input cells.

Excel & Data Problems