'%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 Count Unique Part Numbers Across Multiple Columns in Excel
Question details
The user needs to extract a unique list of part numbers from a data range spanning multiple columns and calculate the occurrence frequency of each distinct part number.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Analyzing inventory or product logs where part numbers are scattered across different rows and columns, requiring a summary of distinct items and their totals.
- Observed behavior
- The goal is to flatten a multi-column range, remove duplicate part numbers, and return the aggregated count of each item in a neat two-column layout.
Ensure your Excel version supports Dynamic Array functions like LET, TOCOL, and UNIQUE, which are available in Microsoft 365 and Excel 2021 or later.
Use LET, TOCOL, and UNIQUE for Dynamic Array Counting
This dynamic array formula flattens the multi-column data, extracts unique values, and calculates the count in a single step.
This method is highly efficient as it does not require helper columns. It combines several modern Excel functions to process the data range directly.
Click on an empty cell where you want the two-column result (Part Number and Count) to begin spilling.
Type the formula `=LET(d, H1:K100, v, UNIQUE(TOCOL(d, 1)), w, COUNTIF(d, v), HSTACK(v, w))` into the formula bar. Replace 'H1:K100' with your actual data range.
Press Enter. The formula will automatically spill the unique part numbers into the first column and their respective counts into the adjacent column.
Use the GROUPBY Function in Newer Excel Versions
If you are using the latest Microsoft 365 updates (Insiders program), the new GROUPBY function simplifies data aggregation significantly.
Analyze Multi-Column Data Effortlessly with WPS Spreadsheet
WPS Office Spreadsheet provides excellent support for dynamic array functions and advanced data analysis tools, allowing you to easily tally inventory and unique part numbers.
- 1. Open Your Inventory File: Launch WPS Spreadsheet and open the document containing your multi-column part numbers.
- 2. Extract Unique Values: Select a blank cell and use the `=UNIQUE()` function combined with your range to generate a distinct list of parts.
- 3. Count the Frequencies: In the adjacent column, use the `=COUNTIF(Original_Range, Unique_Cell)` formula to calculate how many times each part number appears.
- 4. Drag to Fill: Drag the fill handle down to apply the count formula to all the unique part numbers.
Frequently Asked Questions
Why does my formula return a #NAME? error?
This error occurs if your version of Excel does not support modern dynamic array functions like LET, TOCOL, or UNIQUE. You will need Microsoft 365 or Excel 2021 (or later) to use them. For older versions, you can use Pivot Tables or complex traditional array formulas.
Does the TOCOL function ignore blank cells?
Yes, when you set the second argument of the TOCOL function to 1 (e.g., TOCOL(H1:K100, 1)), it automatically ignores any empty cells within the specified range.
How do I sort the results by the highest count?
You can wrap the entire formula in a SORT function. For example, using `=SORT(LET(...), 2, -1)` will sort the output based on the second column (the counts) in descending order.
Can I count part numbers using older Excel versions?
Yes, but it is more tedious. You can press Alt + D + P to open the legacy PivotTable Wizard, select 'Multiple consolidation ranges', and create a Pivot Table that flattens and counts the data across multiple columns.
