Function Problems

How to Use Excel GROUPBY for Monthly Totals with Multiple Criteria

Maira Mehtab Sep 27, 2026 870 views

Question details

The user needs to group transaction data by month, transaction suffix, and category to calculate filtered credit and debit totals using advanced Excel functions.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Organizing and summarizing complex financial transaction lists based on dynamically extracted text strings and specific date formats.
Observed behavior: The user requires a functional formula combination to extract suffixes from descriptions and calculate grouped totals across multiple specified criteria without using standard PivotTables.

Before you start

Ensure you are using a recent version of Microsoft 365, as the GROUPBY, HSTACK, and TEXTAFTER functions are modern array functions not available in older standalone versions of Excel.

Solution 1Recommended

Use GROUPBY, HSTACK, and TEXTAFTER to Calculate Filtered Totals

Combine the GROUPBY function with dynamic array tools like HSTACK and text manipulation functions to extract specific data and summarize it based on custom criteria.

This method uses an advanced array formula to manipulate the raw data in memory before passing it to the GROUPBY function. It extracts the necessary text elements, formats the dates, and filters the summarized output.

Extract the transaction suffix

Use the TEXTAFTER function combined with TRIM to isolate the specific transaction category from your description column. For example, use =TRIM(TEXTAFTER(B1:B16, "-", -1)) to grab the text occurring after the final hyphen and remove any extra spaces.

Stack your criteria columns dynamically

Use the HSTACK function to combine your date formats and extracted text into a single array. Create a sorting key and display column for dates using TEXT(A1:A16, "yyyymm") and TEXT(A1:A16, "mmm-yyyy"), then combine them with your TEXTAFTER formula.

Apply the GROUPBY function with filters

Wrap your HSTACK array inside the GROUPBY function. Specify your value columns (e.g., C1:F16 for credits and debits) and use SUM as the aggregation method. To filter specific categories, use an XLOOKUP combination within the filter_array argument of GROUPBY.

Format the final output

Wrap the entire formula in a DROP function, such as =DROP(GROUPBY(...),,1), to remove the initial "yyyymm" helper column from your final view while maintaining chronological sorting.

Adapting for Structured Tables: If you are using Excel Tables, you will need to manually adapt the standard cell ranges (like A1:A16) in this formula to structured table references (like Table1[Date]) for it to scale automatically with new data.

Summarize Data by Multiple Criteria in WPS Spreadsheet

If you want to group your transaction data by month and category without writing complex nested array formulas, you can easily achieve this using helper columns and powerful PivotTables in WPS Spreadsheet.

1. Create helper columns for Month and Suffix: In your data table, create two new columns. In the first, use =TEXT(A2, "mmm-yyyy") to format the date. In the second, use =TRIM(TEXTAFTER(B2, "-", -1)) to extract the transaction suffix.
2. Insert a PivotTable: Select your entire updated data range, navigate to the 'Insert' tab on the top ribbon, and click 'PivotTable'.
3. Configure your grouping criteria: In the PivotTable fields pane, drag your new 'Month' and 'Suffix' columns into the 'Rows' area to group the data exactly as needed.
4. Calculate totals: Drag your Credit and Debit columns into the 'Values' area. Ensure they are set to 'Sum' to instantly generate your filtered monthly totals.

Fully compatible with Microsoft Excel file formats (.xlsx)Supports advanced text extraction formulas like TEXTAFTER and TRIMPowerful PivotTable interface for intuitive drag-and-drop groupingLightweight, fast, and free to download

Frequently Asked Questions

Why is the GROUPBY function returning a #NAME? error?

The GROUPBY function is a modern addition to Microsoft 365. If you or the person opening the file is using an older, standalone version of Excel (like Excel 2019 or 2016), the function will not be recognized, resulting in a #NAME? error.

How does the TEXTAFTER function work with a negative instance number?

When you use a negative instance number in TEXTAFTER, such as -1, the function searches the text string from right to left instead of left to right. This is highly useful for extracting the very last portion of a string after the final delimiter, regardless of how many delimiters exist before it.

What is the purpose of the DROP function in this formula?

The DROP function is used to remove specific rows or columns from an array. In this scenario, =DROP(...,,1) removes the first column of the resulting GROUPBY array, which was purely a 'yyyymm' helper column used to force chronological sorting.

Can I filter the GROUPBY totals to only include specific categories?

Yes. The GROUPBY function includes a 'filter_array' argument. By using a nested function like NOT(ISERROR(XLOOKUP(...))), you can check your transaction suffixes against a specific list of approved categories and only summarize the matching rows.

Excel & Data Problems