An Excel COUNTIF check is a practical way to confirm that every required workflow status appears in a daily export. Rather than returning only Match or No match, it returns the number of occurrences for each required code. That makes absent statuses immediately visible and also shows where a status appears more than once.
Quick answer: To perform an Excel COUNTIF check, enter =COUNTIF(D2:D8,A2) in B2. With the sample data, B2 returns 1 for New; enter the corresponding formula for each required status to identify a result of 0, which means that status is missing from the daily export.
Set up the required list and daily export
Put the statuses that must appear in the export in column A. Put the exported statuses in column D. Leave column B for the occurrence count. This layout keeps the required list intact and makes each status check easy to review.
Use A2:A5 for New, Open, Packed, and Hold, and use D2:D8 for New, Open, Open, Packed, Shipped, Closed, and Open.
| Required cell | Required status | Export cell | Daily export status |
|---|---|---|---|
| A2 | New | D2 | New |
| A3 | Open | D3 | Open |
| A4 | Packed | D4 | Open |
| A5 | Hold | D5 | Packed |
| D6 | Shipped | ||
| D7 | Closed | ||
| D8 | Open |
Excel COUNTIF check for missing status codes
- Select B2 and enter
=COUNTIF(D2:D8,A2). - Select B3 and enter
=COUNTIF(D2:D8,A3). - Select B4 and enter
=COUNTIF(D2:D8,A4). - Select B5 and enter
=COUNTIF(D2:D8,A5).
Enter =COUNTIF(D2:D8,A2) in B2, =COUNTIF(D2:D8,A3) in B3, =COUNTIF(D2:D8,A4) in B4, and =COUNTIF(D2:D8,A5) in B5.
The first argument, D2:D8, is the export range to search. The second argument is the required status for that row. COUNTIF returns the number of cells in the export range that meet that one criterion.
| Required status | Formula result | Meaning |
|---|---|---|
| New | 1 | Present once |
| Open | 3 | Present three times |
| Packed | 1 | Present once |
| Hold | 0 | Missing from the export |
The counts are 1 for New, 3 for Open, 1 for Packed, and 0 for Hold, so Hold is the missing required status.
Keep the numeric count in column B rather than replacing it with a simple present-or-missing label. The count shows both missing statuses and statuses that occur more than once. For a broader comparison of two lists, see this guide to reconciling two Excel lists.
Important boundary: no matching status
A zero result is a supported and useful boundary outcome. For example, if a three-cell export range contains New, Packed, and Closed, the formula =COUNTIF(A1:A3,"Open") returns 0 because no cell contains Open. A zero is not a formula error; it says the criterion was not found in the searched range.
With the same export list, a count of 0 for Hold is a valid missing-status result, not a formula error; correct the source export or remove Hold from the required list if it is no longer required.
Important limitation: COUNTIF is not case-sensitive
COUNTIF is suitable when status capitalization is not meaningful. Microsoft notes that text criteria in COUNTIF are not case-sensitive. Therefore an export value of OPEN is included when the required value is Open.
COUNTIF ignores upper and lower case, so OPEN and Open both count for an Open criterion; use =EXACT(F2,G2) when two specific status codes must match by case.
For example, put OPEN in F2 and Open in G2. Then enter =EXACT(F2,G2) in H2. The result is FALSE because EXACT compares text case-sensitively. This is a direct two-cell validation, not a replacement for the row-by-row COUNTIF count.
A case-only difference is not caught: with F2 containing OPEN and G2 containing Open, =EXACT(F2,G2) returns FALSE even though COUNTIF treats them as a match.
Common problems when a status seems to be missing
Hidden spaces make text unreliable
Leading or trailing spaces can produce an unexpected COUNTIF result; clean the source values with TRIM before reconciling text codes. If this is a recurring import issue, use the steps in this article on COUNTIF and trailing spaces before running the audit.
Check the range before using the result
The export range must cover all exported rows. If tomorrow’s file extends below D8 but the formula still searches only D2:D8, statuses below D8 will not be counted. Update the range before performing the check.
Compatibility and a simple fallback
Microsoft’s COUNTIF support article lists Excel for Microsoft 365, Excel 2024, and Excel 2021, including the listed Mac releases. If formulas are unavailable, filter the export status column for each required code and record whether any matching rows remain visible.
That fallback can confirm presence, but it does not preserve the repeatable occurrence count that makes COUNTIF useful for a daily check.
Sources
- Microsoft Support: COUNTIF syntax, examples, text-matching behavior, and supported Excel versions
- Microsoft Support: EXACT for case-sensitive text comparisons
Practice with the exact example data
Use a separate blank worksheet for each example. Enter each value at its listed cell address, then type the formula in AA1. Quoted text is shown in JSON notation to preserve spaces: enter the characters inside the quotes, without the quotes, using a leading apostrophe to keep text and spaces literal. An empty quoted string means an explicit empty text value; use an apostrophe entry where appropriate. Compare your result with the expected result below. These are synthetic practice data.
Example 1 — Main case
| Cell | Input value |
|---|---|
| A1 | "Open" |
| A2 | "Packed" |
| A3 | "Open" |
Formula in AA1: =COUNTIF(A1:A3,"Open")
Expected result: 2
Example 2 — Boundary case
| Cell | Input value |
|---|---|
| A1 | "New" |
| A2 | "Packed" |
| A3 | "Closed" |
Formula in AA1: =COUNTIF(A1:A3,"Open")
Expected result: 0
These examples were checked with a limited calculator supporting numeric aggregates, ASCII text functions, exact criteria and exact first-match XLOOKUP. Only the displayed examples and supported semantics were calculated. They were not executed in Microsoft Excel. This check does not verify every claim in the article.
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.