How to Count Unique Checks or Crosses Based on Names in Excel
Question details
The user wants to count the number of unique names that have a specific check mark or cross, ignoring any duplicate name entries.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing status logs, attendance, or task completions where the same individual might appear multiple times, but should only be counted once per status category.
- Observed behavior
- Standard counting functions like COUNTIF count every instance of a check or cross, inflating the total. The goal is to extract a distinct count of individuals per status.
Ensure your dataset contains a column for names and a separate column containing consistent check mark (✓) or cross (✗) symbols without extra spaces.
Use the UNIQUE and FILTER Functions (Modern Excel)
Extract a list of distinct names associated with a specific check or cross mark, then count those unique names.
In modern versions of Excel (Microsoft 365 and Excel 2021), dynamic array formulas provide the easiest way to solve this. By combining FILTER, UNIQUE, and COUNTA, you can isolate the specific marks and count distinct individuals instantly.
Locate your data columns. For example, assume column A contains Names (A2:A20) and column B contains Marks (B2:B20).
Click on an empty cell where you want the result. Type the formula: =COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="✓"))). This filters the names by the check mark, extracts the unique names, and counts them.
To count crosses, replace the check mark in the formula with your cross symbol: =COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="✗"))).
Use a Pivot Table with Distinct Count
Leverage the Data Model feature in Pivot Tables to calculate distinct counts of names based on their check or cross status, suitable for all Excel versions including older ones.
Handle Unique Counts Easily in WPS Spreadsheet
WPS Office offers a robust Spreadsheet application that fully supports modern dynamic array functions and Data Model Pivot Tables, making data summarization a breeze.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your existing Excel workbook containing the names and marks.
- 2. Apply the formula: Use the =COUNTA(UNIQUE(FILTER())) combination exactly as you would in Microsoft Excel to get your unique counts.
- 3. Save seamlessly: Save your document in the standard .xlsx format without worrying about formatting or formula loss.
Frequently Asked Questions
Can I count unique names based on multiple criteria?
Yes, you can add multiple criteria to the FILTER function by multiplying them. For example: =COUNTA(UNIQUE(FILTER(A2:A20, (B2:B20="✓")*(C2:C20="Dept A")))) counts unique names in Department A that also have a check mark.
Why doesn't the UNIQUE function work in my version of Excel?
The UNIQUE and FILTER functions are dynamic array functions introduced in Microsoft 365 and Excel 2021. If you are using an older version like Excel 2016 or 2019, these functions are not available, and you should use the Data Model Pivot Table method instead.
How do I handle empty cells when counting unique names?
Empty cells might accidentally be evaluated as a unique value by the formula. You can exclude them in your FILTER criteria by adding a condition that ensures the cell is not blank, such as (A2:A20<>"").
