'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Find the Top-Selling Product by Subcategory in Excel
Question details
The user needs a method to identify and return the product with the highest sales volume within specific subcategories, including the ability to handle tied results.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing a sales worksheet to determine the best-performing products grouped by subcategory.
- Observed behavior
- The goal is to dynamically extract the exact product codes with the maximum units sold for a given subcategory entered in a specific cell.
Ensure your sales data is organized in clear columns (e.g., Column A for Product Code, Column B for Subcategory, and Column C for Units Sold) and check your spreadsheet software version, as modern functions like FILTER and MAXIFS require Microsoft 365, Excel 2019+, or the latest WPS Office.
Use Modern Dynamic Array Formulas (Recommended)
Combine MAXIFS, FILTER, and TEXTJOIN to quickly find the highest sales and return the corresponding product codes, seamlessly handling multiple tied products in one cell.
This is the most efficient method for modern spreadsheet software. It dynamically calculates the maximum value for the specified subcategory and filters the product list to match that maximum value.
First, find the maximum units sold for the target subcategory (e.g., entered in F4) using the formula: =MAXIFS($C$4:$C$11,$B$4:$B$11,F4).
Use the FILTER function to extract the product codes where both the subcategory matches F4 and the sales match the maximum value calculated in the previous step.
Wrap the entire formula in TEXTJOIN so that if two or more products have the exact same top sales, they are combined in a single cell separated by commas. The final formula looks like this: =TEXTJOIN(", ",TRUE,FILTER($A$4:$A$11,($B$4:$B$11=F4)*($C$4:$C$11=MAXIFS($C$4:$C$11,$B$4:$B$11,F4)))).
Use Array Formulas for Older Versions
If you are using an older version of Excel that lacks MAXIFS or FILTER functions, you can calculate the maximum value using a traditional array formula.
Use Power Pivot for Large Datasets
For massive data models, Power Pivot DAX measures provide a robust way to calculate top-selling products.
Easily Find Top Data with WPS Spreadsheet
WPS Office Spreadsheet fully supports modern dynamic array functions like FILTER, MAXIFS, and TEXTJOIN. Analyze your sales data, find top-performing products, and effortlessly handle complex data sets.
- 1. Open your sales data: Launch WPS Spreadsheet and open the workbook containing your product codes, subcategories, and sales data.
- 2. Select the output cell: Click on the target cell where you want to display the top-selling product for your specific subcategory.
- 3. Enter the combined formula: Type or paste the TEXTJOIN and FILTER formula: =TEXTJOIN(", ",TRUE,FILTER($A$4:$A$11,($B$4:$B$11=F4)*($C$4:$C$11=MAXIFS($C$4:$C$11,$B$4:$B$11,F4)))).
- 4. View the instant results: Press Enter. WPS Spreadsheet will instantly calculate the maximum sales and display the top-selling products, natively handling any ties by separating them with a comma.
Frequently Asked Questions
What happens if there are multiple top-selling products with the exact same sales volume?
By using the recommended formula that incorporates TEXTJOIN, the spreadsheet will return all tied product codes in the same cell, separated by a comma (or whichever delimiter you specify).
Why does my MAXIFS formula return a #NAME? error?
The #NAME? error typically occurs if you are using an older version of Excel (2016 or earlier) that does not support the MAXIFS function. You can either use traditional array formulas or use a modern spreadsheet software like WPS Office to resolve this.
Can I find the lowest-selling product using this same method?
Yes. You can simply replace the MAXIFS function with the MINIFS function in your formula to extract the products with the lowest units sold for a specific subcategory.
