Data Analysis

Excel COUNTIF in a List: Matches and Duplicates

Marc SeanNovember 15, 20267 min read
=IF(B2="","Blank",IF(COUNTIF($A$2:$A$100,B2)>0,"Yes","No"))

COUNTIF(range, criterion) counts cells that meet a condition. When the count is greater than zero, the value appears in the list. The initial IF prevents an empty input from being classified as missing.

If you need the number of occurrences instead of Yes or No, use:

=IF(B2="",0,COUNTIF($A$2:$A$100,B2))

Microsoft documents the function’s syntax, wildcard criteria, and case-insensitive matching in its COUNTIF function reference.

Supplier-code example

This example checks entered supplier codes against a controlled list. Keep in mind that list membership alone doesn’t prove a supplier or transaction is authorized. It confirms only that the entered text matches a value in the referenced cells.

You can copy the Google Sheets template or download the Excel file.

On the List Match Checker sheet:

  • A10:A18 contains the approved codes.
  • B10:B18 contains optional categories.
  • F10:F21 contains entry dates.
  • G10:G21 contains the codes being checked.
  • H10:H21 contains review context.
  • I10:I21 returns Yes, No, or Blank.
  • J10:J21 counts occurrences in the approved list.
  • K10:K21 flags duplicates within the entered batch.

The approved list is:

CellApproved codeCategory
A10SUP-100Office supplies
A11SUP-205Facilities
A12SUP-310IT equipment
A13SUP-412Logistics
A14SUP-518Marketing
A15SUP-623Human resources
A16SUP-707Legal services
A17SUP-845Finance
A18SUP-990Operations

Approved supplier list, COUNTIF batch summary, entered codes, match results, occurrence counts, and duplicate flags.

Return Yes, No, or Blank

Enter this formula in I10 and fill it through I21:

=IF(G10="","Blank",IF(COUNTIF($A$10:$A$18,G10)>0,"Yes","No"))

The dollar signs keep the approved-list range fixed as you copy the formula down. Meanwhile, the reference to G10 changes to G11, G12, and so on.

Expected results include:

  • SUP-100 returns Yes.
  • SUP-999 returns No.
  • An empty record cell returns Blank.

Count approved-list occurrences

To see how many times each entered code appears in the approved list, enter this formula in J10 and fill it through J21:

=IF(G10="",0,COUNTIF($A$10:$A$18,G10))

Interpret the result as follows:

  • 0: The entered value doesn’t appear in A10:A18.
  • 1: It appears once.
  • Above 1: The approved list contains repeated copies of that value.

A zero doesn’t explain why the match failed. The code might be unknown, mistyped, or affected by spaces or other characters.

Flag duplicate entries in the batch

Next, enter this formula in K10 and fill it through K21:

=IF(G10="","Blank",IF(COUNTIF($G$10:$G$21,G10)>1,"Duplicate","Unique"))

Unlike the previous formula, this one counts the current code within the entered batch, not the approved list. When a code occurs twice, both rows are marked Duplicate.

Calculate the batch summary

Use these formulas for the summary in D4:E9:

CellLabelFormula
E4Entered codes=COUNTIF(G10:G21,"<>")
E5In approved list=COUNTIF(I10:I21,"Yes")
E6Not in approved list=COUNTIF(I10:I21,"No")
E7Blank record slots=COUNTIF(I10:I21,"Blank")
E8Rows with repeated code=COUNTIF(K10:K21,"Duplicate")
E9Match rate=IF(E4=0,0,E5/E4)

Format E9 as a percentage. If the entire batch is empty, the zero-denominator condition returns 0.

The baseline batch produces:

RowEntered code in GI: In approved list?J: List occurrencesK: Batch frequency
10SUP-100Yes1Duplicate
11SUP-205Yes1Duplicate
12SUP-999No0Unique
13SUP-310Yes1Unique
14SUP-100Yes1Duplicate
15SUP-518 with a trailing spaceNo0Unique
16SUP-707Yes1Unique
17SUP-412Yes1Unique
18blankBlank0Blank
19SUP-205Yes1Duplicate
20blankBlank0Blank
21blankBlank0Blank

The control totals are:

  • 9 entered codes: 7 matches plus 2 nonmatches.
  • 12 record slots: 9 entered codes plus 3 blanks.
  • 4 rows containing repeated codes: both occurrences of SUP-100 and both occurrences of SUP-205.
  • Match rate: 7 ÷ 9 = 77.78%.

Keep the formulas in every output row, including unused record slots. For example, K18, K20, and K21 return the text Blank; they aren’t empty cells.

These results were recorded using native Google Sheets calculation. The downloadable workbook uses standard Excel formulas, but this specific file wasn’t independently executed in desktop Excel.

Account for COUNTIF wildcards

COUNTIF treats * and ? as wildcard characters. Because the supplier codes above contain neither character, the formulas return the stated results. If your codes might contain wildcards and must be compared literally, use equality comparisons through SUMPRODUCT.

For membership in I10:

=IF(G10="","Blank",IF(SUMPRODUCT(--($A$10:$A$18=G10))>0,"Yes","No"))

For approved-list occurrences in J10:

=IF(G10="",0,SUMPRODUCT(--($A$10:$A$18=G10)))

For batch duplicates in K10:

=IF(G10="","Blank",IF(SUMPRODUCT(--($G$10:$G$21=G10))>1,"Duplicate","Unique"))

Fill each formula through row 21. These versions compare cell values without interpreting * or ? as matching patterns.

Fix apparent matches that return No

In the sample batch, the value in G15 is SUP-518 with a trailing space. It doesn’t equal SUP-518 in the approved list, so the row returns No and 0.

Once you remove the trailing space, the expected results are:

  • I15: Yes
  • J15: 1
  • K15: Unique

The summary then changes to 8 matches, 1 nonmatch, and a match rate of 8 ÷ 9 = 88.89%.

Excel’s TRIM function removes ordinary leading, trailing, and repeated spaces. See Microsoft’s TRIM function documentation.

Whether you should trim automatically depends on the control. Preserving the raw mismatch can help you identify malformed imported codes. Also note that a cell containing only spaces isn’t blank. These formulas classify it as an entered nonmatch rather than Blank.

COUNTIF ignores letter case

COUNTIF isn’t case-sensitive. Changing SUP-100 to sup-100 still matches the approved value and returns an occurrence count of 1.

If uppercase and lowercase codes represent different values, use a case-sensitive comparison method instead. Ordinary COUNTIF isn’t suitable for case-sensitive validation.

Maintain the checker

For each new batch:

  1. Save a copy first if prior batches must be retained.
  2. Update the review date in E2.
  3. Replace only the entries in F10:H21.
  4. Leave E4:E9 and I10:K21 unchanged.
  5. Investigate every No result before relying on the comparison.

The template supports 9 approved-list rows and 12 record rows. If the approved list extends beyond A18 or the batch extends beyond row 21, expand all applicable ranges and fill the row formulas into the added records.

These formulas use commas as argument separators. If your Excel locale expects semicolons, replace the separating commas with semicolons.

For criteria based on several text fragments rather than list membership, see Excel COUNTIF Contains Multiple Criteria.

Use ModelMonkey for this list check

ModelMonkey can inspect spreadsheet context and help with formulas or data-cleanup tasks in Excel and Google Sheets. For this workbook, you could request:

Compare G10:G21 with the approved codes in A10:A18. Keep blank rows classified as Blank, count approved-list occurrences, and flag codes repeated within the entered batch.

When the workflow presents a preview, review the proposed changes before proceeding. Then check the results for an approved code, a missing code, a duplicate, and a blank row. Spreadsheet assistance doesn’t replace checking the formulas and referenced ranges.

Frequently Asked Questions