Function Problems

How to Use Excel GROUPBY and CHOOSECOLS to Sum Filtered Columns

Maira Mehtab Sep 27, 2026 871 views

Question details

The user needs to filter records by a specific search value and sum selected columns grouped by job using Excel functions.

Product: Excel
Device & OS: not provided
Scenario: Filtering data and calculating sums for specific columns based on specific search criteria.
Observed behavior: The goal is to generate a dynamic array where only specified columns are chosen and summed for filtered records.

Before you start

Ensure you are using a version of Excel that supports the latest dynamic array functions, specifically GROUPBY and CHOOSECOLS.

Solution 1Recommended

Combine GROUPBY and CHOOSECOLS to aggregate filtered data

Nest the CHOOSECOLS function inside the GROUPBY function to select specific columns, and apply a filter argument to sum matching records dynamically.

The GROUPBY function allows you to group data and apply an aggregation like SUM. By passing CHOOSECOLS as the value array, you can specify exactly which columns to aggregate. The final argument in the GROUPBY function acts as a filter to restrict calculations to matching records.

Select the destination cell

Click on the cell where you want the summarized data to begin spilling (for example, J2).

Input the combined formula

Enter the formula: =GROUPBY(A2:A7,CHOOSECOLS(B2:F7,1,2,4,5),SUM,0,0,,A2:A7=H2).

Adjust data ranges

Modify the grouping range (A2:A7), the value range (B2:F7), and the search-cell reference (H2) to match your actual worksheet layout.

Execute the formula

Press Enter to generate the results. The formula will automatically spill the filtered and summed data into adjacent cells.

Formula Arguments: In the formula, the two '0's represent the headers and total_depth arguments (meaning no headers and no grand totals), and the empty space before the filter statement is for the sort_order argument.

Powerful Data Analysis in WPS

Group and Sum Filtered Data Easily in WPS Spreadsheet

WPS Spreadsheet provides excellent support for advanced formulas and robust built-in data analysis tools. Whether you prefer complex formulas or intuitive UI features like PivotTables, you can easily summarize and filter your data.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
2. Use PivotTables as a simple alternative: Navigate to the 'Insert' tab and click on 'PivotTable' to analyze data without writing long formulas.
3. Configure your grouping: Drag your search criteria into the 'Filters' area and your job identifiers into the 'Rows' area.
4. Sum the selected columns: Drag the specific columns you want to aggregate into the 'Values' area, ensuring they are set to 'Sum'.

Highly compatible with Microsoft Excel (.xlsx) formulas and functionsSupports dynamic array results for modern data calculationIntuitive PivotTable interface for quick, formula-free data groupingFree to use and incredibly lightweight on system resources

Frequently Asked Questions

What does the CHOOSECOLS function do in this formula?

The CHOOSECOLS function extracts specific columns from an array or range. In this context, CHOOSECOLS(B2:F7,1,2,4,5) tells Excel to only use the 1st, 2nd, 4th, and 5th columns from the range B2:F7 for the sum calculation.

Why am I getting a #NAME? error when using GROUPBY?

The #NAME? error typically occurs if your version of the software does not support the GROUPBY function. Ensure you are using the latest version of Microsoft 365 or a spreadsheet application that has rolled out these dynamic array functions.

Can I use multiple criteria to filter the GROUPBY function?

Yes, you can apply multiple criteria in the filter_array argument of the GROUPBY function by multiplying the conditions. For example, (A2:A7=H2)*(C2:C7="Yes").

Excel & Data Problems