How to Count Nonmatching Values Between Two Excel Lists
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.

- 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.
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.
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.
Click on the empty cell where you want the final count to appear.
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))
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))
Press Enter on your keyboard. The cell will now display the exact count of nonmatching items.

Use Power Query to Find Nonmatching Values
If you have large datasets or need a visual list before counting, Power Query offers a robust alternative.
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. Open your file: Launch WPS Spreadsheet and open the document containing your two lists.
- 2. Select a blank cell: Click on the cell where you want the final nonmatching count to be displayed.
- 3. Enter the formula: Type the formula =COUNTA(FILTER(List1_Range, COUNTIF(List2_Range, List1_Range)=0)) and adjust the ranges accordingly.
- 4. Get your count: Press Enter to instantly see the number of nonmatching values.

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.




