Function Problems

How to Count Matching and Non-Matching ZIP Codes in Excel

Huma Ashraf Ch Oct 21, 2026 868 views

Question details

The user needs to count how many applicant ZIP codes match a predefined list of eligible ZIP codes, and separately count the non-matching ones.

How to Count Matching and Non-Matching ZIP Codes in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing applicant data by comparing a column of entered ZIP codes against a master list of eligible ZIP codes.
Observed behavior: Needs an Excel formula to output two separate counts: one for matching ZIP codes (eligible) and one for non-matching ZIP codes (ineligible).

Before you start

Ensure both your applicant ZIP code list and the eligible ZIP code list share the exact same cell format (either both Text or both Numbers) to avoid formula mismatch errors.

Solution 1Recommended

Use SUM, ISNUMBER, and MATCH Formulas

This combination of functions effectively compares an entire column of data against a reference list and counts the matches using array evaluation.

The MATCH function looks up each applicant's ZIP code in the eligible list. If found, it returns a position number; if not, it returns an error. The ISNUMBER function turns these results into TRUE or FALSE. Finally, the double negative (--) converts TRUE/FALSE into 1/0, allowing the SUM function to add up the total matches.

Count eligible ZIP codes

Select the cell where you want the matching count to appear. Assuming your applicant ZIP codes are in F2:F101 and the list of eligible ZIP codes is in Z2:Z61, enter the formula: =SUM(--ISNUMBER(MATCH(F2:F101,Z2:Z61,0)))

Evaluate the array formula

Press Enter. If you are using an older version of Excel (prior to Microsoft 365), you must press Ctrl+Shift+Enter simultaneously to evaluate it as an array formula.

Count non-eligible ZIP codes

To find the count of applicants who do not match the eligible list, select a different cell and subtract the matching count from the total count of applicants using this formula: =COUNTA(F2:F101)-SUM(--ISNUMBER(MATCH(F2:F101,Z2:Z61,0)))

Adjust Cell Ranges: If your data is located in different columns or rows, simply update the F2:F101 and Z2:Z61 ranges in the formula to correspond with your actual data layout.

Solution 2

Alternative: Use SUMPRODUCT and COUNTIF

A common alternative approach that utilizes SUMPRODUCT and COUNTIF, which often bypasses the need for Ctrl+Shift+Enter in older Excel versions.

Efficient Data Analysis

Use WPS Spreadsheet to Count Data with Ease

WPS Spreadsheet fully supports complex array formulas, MATCH, and ISNUMBER functions. You can seamlessly apply these exact formulas to your applicant data and manage large lists without worrying about version compatibility constraints.

1. Open your worksheet: Launch WPS Spreadsheet and open the file containing your applicant and eligible ZIP codes.
2. Apply the matching formula: Click the target cell and type =SUM(--ISNUMBER(MATCH(F2:F101,Z2:Z61,0))), then press Enter.
3. Apply the non-matching formula: Select the next cell and type =COUNTA(F2:F101)-SUM(--ISNUMBER(MATCH(F2:F101,Z2:Z61,0))) to get the remainder.

Fully compatible with Microsoft Excel formulas and data formatsHandles complex array functions effortlessly without requiring special keystrokesLightweight, fast, and completely free to use for daily data calculation tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my matching formula return zero when I know there are matches?

This usually happens when one column of ZIP codes is stored as text while the other is stored as numbers. Excel sees '12345' (text) and 12345 (number) as completely different values. Highlight your columns and convert them to share the exact same formatting.

What does the double dash (--) do in the SUM and ISNUMBER formula?

The double negative (--) is a mathematical operation that converts the TRUE and FALSE logical values generated by the ISNUMBER function into 1s and 0s. This enables the SUM function to add them up numerically.

Can I just use the COUNTIFS function instead of MATCH?

While COUNTIFS is excellent for applying multiple criteria across a dataset, comparing two entire list arrays against each other is not its primary function. Array formulas combining SUM or SUMPRODUCT with MATCH/COUNTIF are generally required for list-to-list comparisons.

Excel & Data Problems