How to compare and count data from 2 sheets

Use COUNTIF in a helper column to mark each part number from Sheet1 that appears anywhere in Sheet2, then sum those matches and divide by the list count.

Count the matching parts

Assuming:

• Row 1 contains headings.
• Part numbers occupy A2:A14001 on both sheets.
• The sheets are named Sheet1 and Sheet2.

1. On Sheet1, enter this in B2:

      =IF(A2="","",--(COUNTIF(Sheet2!$A$2:$A$14001,A2)>0))
   

2. Fill the formula down through B14001.
   • 1 means the part exists on Sheet2.
   • 0 means it does not.
   • Blank source rows remain blank.

COUNTIF(range, criteria) counts cells meeting one condition; testing whether its result is greater than zero determines whether a match exists ^1^. This cross-sheet pattern is also shown directly in ^2^.

3. Count the common rows:

      =SUM(B2:B14001)
   

4. Count the populated entries in Sheet1:

      =COUNTA(A2:A14001)
   

Calculate the percentage

Percentage of Sheet1 found in Sheet2:

=SUM(B2:B14001)/COUNTA(A2:A14001)

Percentage of Sheet1 not found in Sheet2:

=1-SUM(B2:B14001)/COUNTA(A2:A14001)

Format the result cell as Percentage from Home → Number → Percent Style.

For example, if 12,600 of 14,000 Sheet1 rows appear in Sheet2:

• Common: 90%
• Different/missing: 10%

To measure the reverse direction, repeat the helper formula on Sheet2, referencing Sheet1. This matters when one list contains parts absent from the other.

Important duplicate handling

The procedure counts rows in Sheet1 that have at least one match, not distinct part numbers. If the same part appears three times in Sheet1 and once in Sheet2, all three Sheet1 rows are marked as matches.

If each part should count only once, first make copies of both lists and use Data → Remove Duplicates on column A, then run the formulas against those deduplicated lists.

If expected matches are missing

COUNTIF is not case-sensitive, but leading/trailing spaces or nonprinting characters can prevent expected matches. Microsoft recommends cleaning such data with TRIM or CLEAN ^1^.

In a temporary column, use:

=TRIM(CLEAN(A2))

Fill down, then copy the results and use Paste Special → Values before comparing again.

References

1. Use the COUNTIF function in Microsoft Excel | Microsoft Support
2. If Sheet1, A2 equals Sheet2, H:H, return Yes, No - Microsoft Q&A