If you need an Excel COUNTIF check if value appears exactly once, compare a required-status list with the statuses in your daily export. The count is your control: 1 means the required status appears once, 0 means it is missing, and 2 or more means it is duplicated.
Use =COUNTIF(A2:A7,D2) to count each required status, then label a count of 1 as Valid, 0 as Missing, and any count above 1 as Duplicated. This approach keeps the export unchanged while showing the exception that needs investigation.
Set up an Excel COUNTIF check if value appears exactly once
Place the exported status column on the left and a short list of statuses that must appear on the right. The required list is important: a status that never occurs in the export can still be checked. Looking only for duplicates in the export would not reveal a missing status.
Enter the daily export statuses in A2:A7 as New, Packed, Shipped, Packed, Returned, and Cancelled; enter the required statuses Packed, Shipped, and Delivered in D2:D4.
| A: Export status | D: Required status |
|---|---|
| New | Packed |
| Packed | Shipped |
| Shipped | Delivered |
| Packed | |
| Returned | |
| Cancelled |
Keep the required list separate from the raw export. You can update the export values each day while retaining the formulas and result columns alongside the required list.
Count and classify each required status
- Click E2, next to Packed, and enter
=COUNTIF(A2:A7,D2). - Click E3, next to Shipped, and enter
=COUNTIF(A2:A7,D3). - Click E4, next to Delivered, and enter
=COUNTIF(A2:A7,D4). - In F2, enter
=IF(E2=1,"Valid",IF(E2=0,"Missing","Duplicated")). - In F3 and F4, use the corresponding count cells:
=IF(E3=1,"Valid",IF(E3=0,"Missing","Duplicated"))and=IF(E4=1,"Valid",IF(E4=0,"Missing","Duplicated")).
In E2, enter =COUNTIF(A2:A7,D2), in E3 enter =COUNTIF(A2:A7,D3), and in E4 enter =COUNTIF(A2:A7,D4). In F2, enter =IF(E2=1,”Valid”,IF(E2=0,”Missing”,”Duplicated”)), then use the corresponding E3 and E4 references in F3 and F4.
| Required status | Count formula result | Control result |
|---|---|---|
| Packed | 2 | Duplicated |
| Shipped | 1 | Valid |
| Delivered | 0 | Missing |
The check returns 2 and Duplicated for Packed, 1 and Valid for Shipped, and 0 and Missing for Delivered. This is the useful distinction for a reconciliation check: duplicates and omissions are different failures, even though both require attention.
What the COUNTIF formula is doing
COUNTIF takes two arguments: the range to inspect and the criterion to count. Here, A2:A7 is the export range and D2 is the required status. The formulas use the same export range while changing the required-status reference for each row.
Microsoft describes COUNTIF as a function that counts cells meeting one criterion, including a text criterion supplied by a cell reference. See the official COUNTIF function documentation from Microsoft for its syntax and text-criteria guidance.
The classification formula deliberately tests for 1 first. That is the only passing result. A zero is not “fine because there is no duplicate”; it is an absent required status. Any value greater than 1 is a duplicate status that should be traced back to its source rows.
Boundary cases that can change the result
A status is missing entirely
If a required status is absent, COUNTIF returns 0; keep that required status in the checklist so the Missing result remains visible. In the sample, Delivered is not in A2:A7, but it is still tested because it appears in D4. Add every required workflow status to column D, including statuses that are rare.
A status appears more than once
If the export contains Packed twice, COUNTIF returns 2; investigate both records rather than using Remove Duplicates, which can hide the reconciliation issue. This is a boundary result because 2 is the first count above the exactly-once rule. Removing a row may be appropriate later, but it changes the evidence before you know why the repeat occurred. First identify the order IDs, timestamps, or source-system events associated with both records.
Case and hidden-character differences
COUNTIF does not distinguish uppercase from lowercase text, so Packed and PACKED count as the same status. Standardize the expected casing if it matters operationally, but do not use this count alone to enforce capitalization. Conversely, extra leading or trailing spaces can prevent a visible match from counting as expected.
Leading or trailing spaces can produce an unexpected count, so clean inconsistent status text before relying on the control. Check the source value first, particularly after copy-and-paste imports. A count of zero may indicate a text-cleanliness problem rather than a genuinely missing workflow status.
Compatibility and practical limits
COUNTIF is available in Excel for Microsoft 365, Excel 2024, Excel 2021, and their Mac versions; the same formula also works in older desktop versions that include COUNTIF. No dynamic-array feature, add-in, VBA, or Power Query step is required for this small control.
This pattern evaluates one criterion at a time. If your audit also needs to confirm that a status appears once for each order ID, use a multi-condition approach rather than counting the status column alone. Likewise, COUNTIF is not a formatting check: it cannot count cells based only on fill or font color.
A useful next reconciliation step
After the status count flags an exception, compare the affected records with the expected source list. Our guide to reconciling two Excel lists helps when you need to identify which records exist in one list but not the other.
For a related single-status audit, see how to use COUNTIF to check status codes. That pattern is useful when the question is whether a particular code occurs, while this checklist pattern is designed to distinguish exactly-once, missing, and duplicated required statuses.
Prepared with automated drafting and review, official references, and a limited example calculator. Synthetic examples are provided for practice; no independent human review or Microsoft Excel execution is claimed.