How to Count Unique Employees and Sum Monthly Amounts by Category in Excel
Question details
The user needs to group transaction data by month and employee to sum their totals, count the unique employees, and categorize them into specific amount ranges such as 500 or less and 501–1000.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing monthly employee transaction data to count unique individuals and summarize amounts grouped by specific financial tiers.
- Observed behavior
- Requires combining advanced aggregation and filtering functions to summarize raw transaction data into categorized total reports.
Ensure your transaction data is formatted as an Excel Table (e.g., named 'Demo') to make formula references dynamic, and check that you are using a version of Excel that supports Dynamic Arrays and the GROUPBY function.
Use the GROUPBY and LET Functions to Categorize and Sum
Leverage modern dynamic array functions to quickly group dates, sum amounts, and categorize based on employee totals in a single formula.
This approach works best in Microsoft 365 where the GROUPBY function is available. It categorizes the data into brackets (e.g., 500 or less, over 500) and stacks the results.
Keep in mind that if the category must be based on the total per employee rather than individual transactions, you must first aggregate the transactions for each employee within each month before assigning the resulting employee total to an amount category.
Select your raw transaction data containing columns for Date, Employee, and Amount. Press Ctrl+T to convert it into an Excel Table and name the table 'Demo' in the Table Design tab.
Select an empty cell where you want the summary report to start. Input the formula: =LET(at_most_500, Demo[Amount]<=500, over_500, Demo[Amount]>500, functions, HSTACK(COUNT,SUM), a, GROUPBY(Demo[Date], Demo[Amount], functions, , 0, , at_most_500), b, GROUPBY(Demo[Date], Demo[Amount], functions, , 0, , over_500), VSTACK(a,b))
Press Enter. The dynamic array formula will automatically spill the results, grouping the dates, counting unique entries, and summing the categories based on the defined brackets.
Use Pivot Tables and Helper Columns for Broader Compatibility
If your version of Excel does not support the GROUPBY function, use helper columns to aggregate employee totals before categorizing them with a Pivot Table.
Easily Summarize Data Using Advanced Formulas in WPS Spreadsheet
WPS Office fully supports dynamic array functions and advanced data analysis tools. You can smoothly open your existing Excel workbooks and run powerful summary formulas like UNIQUE, FILTER, and LET to categorize data without complex workarounds.
- 1. Open your data in WPS: Launch WPS Spreadsheet and open your existing transaction workbook.
- 2. Apply dynamic arrays: Select the target cell and type in your dynamic array formulas like LET, UNIQUE, or FILTER to extract and summarize your data.
- 3. Expand and group: Use built-in Pivot Tables or dynamic ranges to group your employee transaction arrays instantly.
- 4. Save your work: Save your categorized report directly as an .xlsx file to share flawlessly with other users.
Frequently Asked Questions
What if my version of Excel doesn't support the GROUPBY function?
You can use a combination of Pivot Tables or helper columns alongside the SUMIFS and COUNTIFS functions. Calculate the total amounts per employee in a new column first, then summarize those records into categories.
How do I categorize totals into specific ranges like 501-1000 using formulas?
Inside your LET function or helper column, you can establish multiple boolean conditions by multiplying them, such as (Demo[Amount]>500)*(Demo[Amount]<=1000). This acts as an AND condition to filter those specific records.
Can I count unique employees within a Pivot Table instead of using formulas?
Yes. When creating your Pivot Table, be sure to check the box 'Add this data to the Data Model'. This unlocks the 'Distinct Count' option in the Value Field Settings, allowing you to automatically count unique employees without complex formulas.
