How to Sum Categories by Date Using Excel SUMIFS Alternatives
Question details
The user needs to match production dates with item categories across multiple tables and summarize the total units by category and date without relying solely on traditional SUMIFS.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Summarizing inventory or production data across separate worksheets containing SKU codes, categories, and daily production units.
- Observed behavior
- The user wants to identify each item's category and sum the units grouped by date and category using advanced formulas, Power Query, or PivotTables.
Ensure your data is formatted as official Excel Tables (e.g., Table1, Table2) to make formula referencing and Power Query operations seamless.
Use PIVOTBY and XLOOKUP Dynamic Array Formulas
Ideal for Microsoft 365 users, this method dynamically looks up categories and pivots the data in a single formula.
The newly introduced PIVOTBY function in Excel allows you to group, aggregate, and pivot data directly within a cell using arrays, eliminating the need for traditional PivotTables in simple scenarios.
Click on an empty cell where you want the top-left corner of your summary table to begin.
Enter the following formula: =LET(category, XLOOKUP(Table2[Item Code], Table1[Item Code], Table1[Category], "None"), PIVOTBY(Table2[Date], category, Table2[Units], SUM, , 0, , 0)).
Press Enter. The formula will automatically spill the summarized units grouped by date and category across the adjacent rows and columns.
Merge and Pivot Data Using Power Query
Best for large datasets where you want an automated, repeatable data transformation process.
Use SUMPRODUCT and XLOOKUP
A great alternative formula approach for users who do not have access to the PIVOTBY function.
Summarize Categories by Date Effortlessly with WPS Office
WPS Spreadsheet fully supports advanced functions like XLOOKUP and SUMPRODUCT, as well as powerful PivotTables, allowing you to seamlessly summarize your production categories by date just like in Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your SKU, Production, and Expected tables.
- 2. Fetch categories with XLOOKUP: In your Production table, insert a new column and use the XLOOKUP function to pull category data from your SKU table for each Item Code.
- 3. Insert a PivotTable: Select your updated Production table, go to the 'Insert' tab, and click 'PivotTable'.
- 4. Configure the PivotTable: Drag 'Date' to the Rows area, 'Category' to the Columns area, and 'Units' to the Values area to instantly generate your cross-tabulated summary.
Frequently Asked Questions
What if my Excel version doesn't support the PIVOTBY function?
If you are not on the latest Microsoft 365 update, you can use the SUMPRODUCT and XLOOKUP combination, or simply add a helper column for the Category and build a standard PivotTable.
How do I add multiple tables to the Excel Data Model?
On Windows, click on any cell in your formatted table, go to the Power Pivot tab (or Data tab), and select 'Add to Data Model'. Repeat this for all tables, then create relationships between them using the common 'Item Code' column in the Diagram View.
Why does my SUMPRODUCT formula return a #VALUE! error?
This usually happens if the ranges in your SUMPRODUCT formula are not exactly the same size. Ensure that the 'Units', 'Date', and 'Item Code' columns referenced from Table2 all contain the exact same number of rows.
Can I use VLOOKUP instead of XLOOKUP for this summary?
Yes, you can use VLOOKUP to retrieve the category for each item code. Add a helper column in your Production table with the VLOOKUP formula, then build a PivotTable or use SUMIFS based on that newly populated column.
