How to Count Unique Names with an Upcoming Status but No Closed Status
Question details
The user needs to count the number of distinct individuals who have an "Upcoming" status, but strictly exclude anyone who has previously been marked with a "Closed" status in the dataset.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing a list of people where individuals appear multiple times with different statuses, and calculating a distinct count of people conditionally based on two specific status criteria.
- Observed behavior
- Standard counting functions fail because individuals have multiple entries. The goal state requires a dynamic formula to cross-reference an "Upcoming" group against a "Closed" group and count the non-overlapping unique entries.
Verify that your spreadsheet software supports dynamic array functions (such as LET, UNIQUE, and FILTER) and ensure your data ranges for names and statuses are of equal length.
Use LET, FILTER, UNIQUE, and XMATCH (Recommended)
This dynamic array formula defines separate lists for 'Upcoming' and 'Closed' entries, then counts how many 'Upcoming' names do not exist in the 'Closed' list.
The LET function allows you to define variables within your formula, making it easier to read and process. By filtering out unique names based on their status, you can compare the two lists directly using XMATCH.
Determine the columns for your names and statuses. For this example, assume Names are in A2:A13 and Statuses are in B2:B13.
Select an empty cell and input the following formula: =LET(names, A2:A13, status, B2:B13, upcoming, UNIQUE(FILTER(names, status="Upcoming")), closed, UNIQUE(FILTER(names, status="Closed")), SUM(ISNA(XMATCH(upcoming, closed))*1))
Press Enter. The formula will extract all unique 'Upcoming' names, check if they exist in the 'Closed' array using XMATCH, and sum the errors (ISNA) to count only those that did not find a match.
Use SUMPRODUCT and COUNTIFS for Older Versions
If your spreadsheet software does not support LET or XMATCH, you can achieve the same result using a combination of SUMPRODUCT, UNIQUE, and COUNTIFS.
Easily Handle Dynamic Arrays with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like LET, UNIQUE, FILTER, and XMATCH out of the box. You can execute complex multi-condition counts effortlessly without upgrading to expensive alternatives.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your names and statuses.
- 2. Paste the formula: Click on any empty cell where you want to display the count and paste the provided LET formula.
- 3. Update range references: Adjust the 'A2:A13' and 'B2:B13' parts of the formula to match your exact data columns, then press Enter.
Frequently Asked Questions
Why do different filtering formulas give slightly different total counts?
A discrepancy usually occurs because of how criteria are evaluated. For example, one formula might explicitly count names that have an 'Upcoming' status and no 'Closed' status, while another formula might mistakenly count ANY name that doesn't have a 'Closed' status, even if their status is 'Pending' or 'New' instead of 'Upcoming'.
What happens if a name appears with an 'Upcoming' status after a 'Closed' status?
The recommended LET formula checks if the name appears in the 'Closed' list at all, regardless of the chronological order or row position. If the name has a 'Closed' status anywhere in the defined range, it will be excluded from the final count.
Are functions like UNIQUE and FILTER case-sensitive?
No, standard functions like UNIQUE, FILTER, and XMATCH are not case-sensitive. 'UPCOMING' and 'Upcoming' will be treated as the exact same status when applying these formulas.
