Function Problems

How to Count Nonmatching Values Between Two Excel Lists

Ayan MasoodAyan Masood Oct 20, 2026 870 views

Question details

The user needs to calculate the total number of values that appear in one list but not in another, directly returning a count without relying on separate subtraction calculations.

How to Count Nonmatching Values Between Two Excel Lists
Product
Excel
Device & OS
not provided
Scenario
Comparing two lists of data to find the exact count of unique, nonmatching items in either list.
Observed behavior
Requires a dynamic array formula approach to directly identify and output the number of nonmatching items.
Before you start

Ensure you are using a version of Excel or WPS Spreadsheet that supports dynamic array functions such as FILTER, as these are required to perform this action in a single cell.

Solution 1Recommended

Use Dynamic Array Formulas (FILTER and COUNTIF)

The most efficient way to get a direct count of nonmatching values between two lists using a single nested formula.

You can combine the COUNTIF and FILTER functions to isolate the items that do not appear in the second list, and then use the COUNTA function to count those filtered results.

1
Select the destination cell

Click on the empty cell where you want the final count to appear.

2
Count items in the first list missing from the second

To count values in column A (e.g., A2:A11) that do not appear in column C (e.g., C2:C5), enter the formula: =COUNTA(FILTER(A2:A11,COUNTIF(C2:C5,A2:A11)=0))

3
Count items in the second list missing from the first

To count values in column C that do not appear in column A, enter the formula: =COUNTA(FILTER(C2:C5,COUNTIF(A2:A11,C2:C5)=0))

4
Execute the formula

Press Enter on your keyboard. The cell will now display the exact count of nonmatching items.

Use Dynamic Array Formulas (FILTER and COUNTIF)
Adjust Cell Ranges: Make sure to adjust the references 'A2:A11' and 'C2:C5' to match the actual data ranges in your specific spreadsheet.

Count and Compare Values Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like FILTER and COUNTIF, allowing you to instantly compare lists and calculate nonmatching values without tedious manual work.

  1. 1. Open your file: Launch WPS Spreadsheet and open the document containing your two lists.
  2. 2. Select a blank cell: Click on the cell where you want the final nonmatching count to be displayed.
  3. 3. Enter the formula: Type the formula =COUNTA(FILTER(List1_Range, COUNTIF(List2_Range, List1_Range)=0)) and adjust the ranges accordingly.
  4. 4. Get your count: Press Enter to instantly see the number of nonmatching values.
100% compatible with Microsoft Excel formulas and functionsFull support for advanced dynamic array formulasFree, lightweight, and fast performance for large datasetsFamiliar ribbon interface ensures a zero-learning-curve transition
microsoft office alternative - wps office

Frequently Asked Questions

How do I count matching values instead of nonmatching ones?

To count matching values, change the '=0' portion of your COUNTIF condition to '>0'. For example, use =COUNTA(FILTER(A2:A11,COUNTIF(C2:C5,A2:A11)>0)).

Why is my FILTER formula returning a #CALC! error?

If the FILTER function finds no nonmatching values (meaning all items perfectly match), it returns a #CALC! error. You can wrap your formula in the IFERROR function, like =IFERROR(COUNTA(...), 0), to display a 0 instead of an error.

Can I use VLOOKUP to count nonmatching values?

While VLOOKUP can identify nonmatching values by returning an #N/A error, counting those errors requires combining it with ISNA and SUMPRODUCT. Using the FILTER and COUNTIF method is much cleaner and more direct for counting.