Quick answer
To check whether a required supplier code is absent from an imported order list, enter =COUNTIF(A1:A3,"SUP-A") in B1. A result of 0 means that no cell in A1:A3 matches SUP-A under COUNTIF’s text-matching rules.
This is a small but useful reconciliation control before you match orders to a supplier master or process an accounts-payable export. COUNTIF counts the cells in a range that meet one criterion; Microsoft documents the syntax as COUNTIF(range, criteria). Microsoft’s COUNTIF guidance also covers text criteria and common text-data issues.
Set up the supplier-code check
Use this method when one supplier code must appear at least once in an imported list. It is deliberately a count, rather than a lookup: the result tells you whether the required value is absent, present once, or repeated.
Synthetic input: A1 contains SUP-A, A2 contains SUP-B, and A3 contains SUP-C; B1 contains =COUNTIF(A1:A3,"SUP-A").
| Cell | Value or formula | Purpose |
|---|---|---|
| A1 | SUP-A | Imported supplier code |
| A2 | SUP-B | Imported supplier code |
| A3 | SUP-C | Imported supplier code |
| B1 | =COUNTIF(A1:A3,"SUP-A") | Required-code count |
With SUP-A, SUP-B, and SUP-C in A1:A3, B1 returns 1 because the required supplier code occurs once.
Check a required supplier code step by step
- Put the supplier codes from the import in one column. In this example, the imported range is A1:A3.
- Decide which code must be present. The required code in this control is SUP-A.
- Select an output cell outside the imported range, B1 in this example.
- Enter
=COUNTIF(A1:A3,"SUP-A")and press Enter. - Interpret the output before continuing the reconciliation: 0 is absent, 1 is one matching entry, and any number above 1 is more than one matching entry.
Keep the range limited to the imported supplier-code field. Including a supplier master list or a previous import in A1:A3 would make the count look reassuring even if the current order import omitted the code.
How the formula works
A1:A3 is the range COUNTIF examines. "SUP-A" is the single text criterion. COUNTIF counts each cell in that range that matches the criterion and returns the count in B1. Microsoft similarly describes COUNTIF as a way to count how often a particular value occurs in a range in its guide to counting value occurrences.
A result of 1 is normally the expected outcome for this synthetic list. It confirms presence, but it does not prove that the surrounding order data are complete or valid. The check is best treated as one clear control within a wider reconciliation.
Handle the no-match boundary case
If A1 contains SUP-B, A2 contains SUP-C, and A3 contains SUP-D, the same formula in B1 returns 0. Treat that zero as an absence exception: check the import and do not reconcile the order list as though SUP-A were present.
| Cell | Boundary input |
|---|---|
| A1 | SUP-B |
| A2 | SUP-C |
| A3 | SUP-D |
| B1 | =COUNTIF(A1:A3,"SUP-A") returns 0 |
Do not replace a zero with a manual assumption that the supplier is simply new. First confirm that the source export used the expected supplier-code field and that the import was not filtered or truncated.
What 0, 1, and multiple matches mean
| Result in B1 | Meaning | Next action |
|---|---|---|
| 0 | The required code was not counted in the selected import range. | Investigate the export, field selection, and text quality. |
| 1 | One cell matched the required code. | Continue with the next reconciliation control. |
| 2 or more | More than one cell matched the code. | Review whether duplicate supplier entries are expected. |
A result greater than 1 means more than one cell matched the required code. It signals a duplicate or repeated supplier-code entry to investigate, not proof that the import is correct.
Common problems and limits
If B1 returns 0 although the code looks present, inspect the imported value for leading or trailing spaces and nonprinting characters, then clean the import before rerunning the check. Text imported from another system can contain characters that are not visible in the worksheet but prevent the expected match.
COUNTIF does not distinguish upper- and lowercase text, so a case-only difference will not produce an absence result. Use a case-sensitive comparison process if code casing is part of the control.
This control establishes only whether the required text occurs in the selected imported range; it does not validate the supplier against a master record, identify the matching row, or decide whether multiple occurrences are acceptable. For a broader list-to-list process, see this guide to reconciling two Excel lists. If you need to apply the same counting logic to controlled labels, the approach in checking status codes with COUNTIF is a useful next step.
Compatibility
COUNTIF is available in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, including the Microsoft-documented Windows and Mac editions where listed. The formula uses a long-established worksheet function, so no dynamic-array feature or add-in is required for this one-cell control.
Sources
- Microsoft Support: Use the COUNTIF function in Microsoft Excel
- Microsoft Support: Count how often a value occurs in Excel
Worked example details and boundary cases
Method
Keep the imported supplier-code column in A1:A3, place the required code directly in the COUNTIF criterion, and use the number returned in B1 as the reconciliation control.
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 | “SUP-A” |
| A2 | “SUP-B” |
| A3 | “SUP-C” |
Formula in AA1: =COUNTIF(A1:A3,"SUP-A")
Expected result: 1
Example 2 — Boundary case
| Cell | Input value |
|---|---|
| A1 | “SUP-B” |
| A2 | “SUP-C” |
| A3 | “SUP-D” |
Formula in AA1: =COUNTIF(A1:A3,"SUP-A")
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.
Synthetic examples are provided for practice. They are not evidence of personal experience, independent human review or execution in Microsoft Excel.
Download the practice sheet (HTML) or Download the practice workbook (XLSX). The workbook has exact input cells and expected results; enter the provided formula yourself. Available after this article is published.