'%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 Transmittal Numbers in Excel
Question details
The user needs a formula to count distinct transmittal numbers from a list containing repeated identifiers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains multiple documents belonging to the same transmittal, resulting in duplicate identifiers. The goal is to accurately calculate the number of distinct transmittal numbers.
- Observed behavior
- The user is seeking the correct functions to evaluate the dataset and return a single numerical count of non-repeating or distinct transmittal identifiers.
Verify your Excel version, as newer versions support dynamic array functions like UNIQUE, while Excel 2019 and older require legacy array formulas.
Use the UNIQUE and COUNTA Functions (Modern Excel)
For Microsoft 365, Excel 2021, and newer versions, combining COUNTA with UNIQUE is the most efficient way to count distinct identifiers.
The UNIQUE function automatically filters out duplicate values from an array. By wrapping it in the COUNTA function, you can count the number of items remaining in that filtered list.
Click on the empty cell where you want the distinct count result to be displayed.
Type the formula =COUNTA(UNIQUE(A2:A11)) into the formula bar. Replace A2:A11 with the actual range containing your transmittal numbers.
Press the Enter key. Excel will evaluate the formula and display the total number of distinct transmittal identifiers.
Use SUMPRODUCT and COUNTIF (Excel 2019 and Older)
If you are using Excel 2019 or an older version where the UNIQUE function is unavailable, use this legacy formula to count distinct non-blank values.
Count Identifiers Occurring Exactly Once
Use this method if you need to count only the transmittal numbers that are completely unique and never repeat in the list.
Count Unique Values Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like UNIQUE and SUMPRODUCT, making it easy to process distinct data counts without compatibility issues.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your transmittal numbers.
- 2. Enter the formula: Click on an empty cell and type =COUNTA(UNIQUE(A2:A11)) for modern evaluation.
- 3. Get the count: Press Enter to instantly display the distinct transmittal number count.
Frequently Asked Questions
What is the difference between unique and distinct in Excel?
Distinct refers to all the different values in a list, counting each different value once regardless of how many times it repeats. Unique strictly refers to values that appear exactly one time in the entire dataset without any duplicates.
Why does my UNIQUE formula return a #NAME? error?
The #NAME? error occurs if you are using an older version of Excel (like Excel 2019, 2016, or older) that does not support the dynamic array UNIQUE function. If you encounter this, switch to the SUMPRODUCT formula alternative.
Can I count unique values while ignoring blank cells?
Yes. The legacy formula =SUMPRODUCT((A2:A11<>"")/COUNTIF(A2:A11,A2:A11&"")) specifically ignores blank cells using the <>"" condition. In modern Excel, you can wrap the FILTER function inside UNIQUE to exclude blanks, like this: =COUNTA(UNIQUE(FILTER(A2:A11, A2:A11<>""))).
