How to Summarize Excel Data by Client, SKU, and Period
Question details
Create a summary table that totals values for each client and SKU across multiple period start dates, even when the product descriptions contain embedded SKUs.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating a financial or inventory summary report from raw data where SKUs are mixed within description strings and dates need to be normalized into specific periods.
- Observed behavior
- The raw data cannot be directly aggregated using traditional pivot tables because the SKUs are not isolated in their own column and the period dates are not standardized.
Ensure your source data is formatted as an Excel Table (e.g., named 'DemoTbl') so that your formula references automatically update when new data is added.
Use the PIVOTBY and LET Functions to Dynamically Summarize Data
This method uses Excel's dynamic array functions to clean the data, extract the SKU, normalize the dates, and generate a pivot table entirely via a single formula.
By combining the LET function with newer array functions like REGEXEXTRACT and PIVOTBY, you can perform data cleaning and aggregation in one step without needing helper columns.
Within your LET function, use the EOMONTH function to normalize your start dates to the first day of the month: dates, EOMONTH(DemoTbl[Start Date], -1) + 1.
Use REGEXEXTRACT to pull the SKU number from the product description string. For example: product, REGEXEXTRACT(DemoTbl[Product], "(SKU\\d+)").
Use the HSTACK function to place the Client Name and the extracted SKU side by side to form your row labels: row_labels, HSTACK(DemoTbl[Client name], product).
Finally, use the PIVOTBY function to assemble the table. The full formula looks like this: =LET(dates, EOMONTH(DemoTbl[Start Date],-1)+1, product, REGEXEXTRACT(DemoTbl[Product],"(SKU\\d+)"), row_labels, HSTACK(DemoTbl[Client name],product), values, DemoTbl[Value], PIVOTBY(row_labels, dates, values, SUM)).
Use SUMIFS for a Fixed Summary Layout
If you prefer a static table structure or are using a version of Excel that does not support PIVOTBY, you can use helper columns and SUMIFS.
Summarize Complex Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful built-in Pivot Tables, advanced text functions, and comprehensive tools to clean, extract, and summarize raw data effortlessly.
- 1. Clean Your Data: Use WPS Spreadsheet's text functions or Flash Fill feature to extract the embedded SKUs into a new dedicated column.
- 2. Insert a Pivot Table: Select your cleaned data range, navigate to the 'Insert' tab, and click on 'PivotTable'.
- 3. Configure Rows and Columns: Drag the 'Client' and your newly extracted 'SKU' fields into the Rows area, and place your 'Period' date field into the Columns area.
- 4. Summarize Values: Drag the 'Value' field into the Values area to automatically generate a comprehensive summary layout.
Frequently Asked Questions
How can I extract an SKU from a string if I don't have the REGEXEXTRACT function?
You can use a combination of Excel's standard text functions such as MID, FIND, or SEARCH. Alternatively, if the data pattern is consistent, you can type the first SKU manually in an adjacent column and use Flash Fill (Ctrl + E) to extract the rest.
Why is it important to use EOMONTH to normalize dates?
Raw data often contains transaction dates from various days within a month. Using EOMONTH forces all dates in a given month to share the exact same start date (e.g., the 1st of the month), ensuring they roll up correctly under a single column header in your summary.
What is the primary difference between using PIVOTBY and a traditional PivotTable?
PIVOTBY is a dynamic formula function that automatically spills the row headers, column headers, and summarized values directly onto the grid. A traditional PivotTable uses an interactive interface that allows for manual drag-and-drop customization and formatting without writing complex formulas.
Can I use the SUMIFS method without creating helper columns?
Generally, no. SUMIFS requires ranges that actually exist in the worksheet to evaluate criteria. If your SKUs are embedded inside larger text strings, you must first extract them into a helper column so SUMIFS has a clean target range to check against.
