How to Count Each Sales ID Once by Products Sold in Excel
Question details
The user needs to group records by Sales ID and count each ID exactly once by categorizing them according to the unique products they sold (e.g., specific products, combined categories, or nothing sold).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing a sales dataset containing multiple rows per Sales ID, various products, and Yes/No sold values, where IDs must be categorized uniquely without duplicating counts for multi-product sales.
- Observed behavior
- Basic formulas like COUNTIFS lead to duplicate counting or fail to properly classify multi-product combinations and 'Sold Nothing' scenarios.
Ensure your dataset is organized in a tabular format with clear column headers for Sales ID, Product, and Sold Status (Yes/No). Verify that your spreadsheet software supports dynamic array functions (like Excel 365 or recent versions of WPS Office).
Use Dynamic Array Functions to Summarize and Categorize Data
Create a summary table that extracts unique Sales IDs, evaluates their corresponding products, and assigns an aggregated category before performing a final count.
By nesting functions such as UNIQUE, FILTER, LET, and TEXTJOIN, you can dynamically evaluate all rows for a given Sales ID. This prevents an ID with multiple products from being counted multiple times or incorrectly grouped.
Select a blank column for your summary table and use the UNIQUE function (e.g., `=UNIQUE(A2:A100)`) to generate a list containing one row per Sales ID.
In the adjacent column, use the LET and FILTER functions to isolate products where the Sold status equals 'Yes'. Wrap this in a UNIQUE function to remove duplicate product names for the same ID.
Use TEXTJOIN within your formula to combine multiple product names (e.g., 'Apples and Oranges'). Add an IF statement to return 'Sold Nothing' if the FILTER function returns no results.
Once your summary table lists each Sales ID alongside its accurate single or combined category, use the COUNTIF function on the category column to count how many times 'Apples Only', 'Apples and Oranges', or 'Sold Nothing' occur.
Restructure Data Using Helper Columns and Pivot Tables
For older versions of Excel that do not support dynamic arrays, use helper columns to flag combinations and summarize them via a Pivot Table.
Effortlessly Categorize Complex Sales Data in WPS Office
WPS Spreadsheet fully supports modern dynamic array formulas, allowing you to use UNIQUE, FILTER, LET, and TEXTJOIN seamlessly to categorize multi-condition sales data without needing expensive software subscriptions.
- 1. Open your sales data workbook: Launch WPS Spreadsheet and open the file containing your Sales IDs, Products, and Sold Status.
- 2. Generate a unique ID list: Select a blank cell and input the =UNIQUE() formula targeting your Sales ID column.
- 3. Filter for successful sales: Use the =FILTER() function to extract only the products that have 'Yes' in the Sold column for each unique ID.
- 4. Combine product names: Wrap your filtered results in =TEXTJOIN() to create unified category names like 'Apples and Pears'.
- 5. Tally the categories: Apply the =COUNTIF() formula to your new summary column to calculate the exact count for each product combination.
Frequently Asked Questions
Can I use COUNTIFS to categorize multiple products per Sales ID?
COUNTIFS is excellent for counting rows that meet single or multiple static criteria, but it struggles to dynamically output combined text categories (like 'Apples and Oranges') uniquely per Sales ID. Dynamic array functions or Helper columns are required for this type of grouping.
How do I calculate the 'Sold Nothing' category?
You can calculate 'Sold Nothing' by checking if the total count of 'Yes' values for a specific Sales ID equals zero. In a dynamic formula, this can be achieved using an IF statement alongside the FILTER function's 'if_empty' argument.
Why are UNIQUE and FILTER functions returning a #NAME? error?
The #NAME? error typically appears if you are using an older version of Excel (like Excel 2016 or 2019) that does not support dynamic array functions. You must upgrade to Office 365, Excel 2021, or use a modern alternative like WPS Office.
How can I remove duplicate product names before classifying them?
To prevent a product from being listed twice for the same Sales ID (e.g., 'Apples and Apples'), wrap your FILTER function inside another UNIQUE function in your formula. This ensures only distinct products are passed to the TEXTJOIN categorization step.
