Function Problems

How to Count Unique Matches Between Two Excel Columns

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to count the number of unique, non-blank values that appear in both column K and column L without counting duplicate entries.

Product: Excel
Device & OS: not provided
Scenario: Comparing two lists of data, such as phone numbers, to accurately calculate how many distinct items exist in both lists.
Observed behavior: Using basic COUNT and MATCH functions incorrectly inflates the total by counting duplicate values multiple times and sometimes including blank cells.

Before you start

Ensure you are using a modern version of Excel (Microsoft 365, Excel 2021) or WPS Office Spreadsheet, as this solution relies on dynamic array functions like UNIQUE and FILTER.

Solution 1Recommended

Use COUNTA, UNIQUE, and FILTER Functions

Combine dynamic array functions to effectively filter out blanks, check for matches across columns, and ensure duplicates are only counted once.

The MATCH function alone returns a result for every matching value, meaning duplicates in your primary column are counted multiple times. To fix this, we combine UNIQUE to extract distinct values, FILTER to remove blanks and non-matches, and COUNTA to count the final cleaned list.

Select the destination cell

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

Enter the nested formula

Type the formula: =COUNTA(UNIQUE(FILTER(K4:K226,(K4:K226<>"")*(COUNTIF(L4:L226,K4:K226)>0)))). Be sure to adjust the ranges K4:K226 and L4:L226 if your data is located in different rows or columns.

Apply the calculation

Press Enter to execute the formula. The software will process the arrays and display the total number of unique matching records.

How the Logic Works: The (K4:K226<>"") segment ignores empty cells, while the COUNTIF segment acts as a logical test to only keep values that appear greater than 0 times in the secondary column.

Advanced Data Analysis

Count Unique Matches Easily in WPS Office

WPS Office Spreadsheet fully supports modern dynamic array formulas like UNIQUE, FILTER, and COUNTA. You can perform complex data comparisons and count unique matches without worrying about duplicate errors, all within a lightweight and highly compatible environment.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the two columns you want to compare.
2. Input the array formula: Select an empty cell and enter the =COUNTA(UNIQUE(FILTER(...))) formula to calculate unique matches.
3. View the accurate count: Hit Enter to instantly get the precise number of unique, non-blank matches between your data columns.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports advanced dynamic array functions like UNIQUE and FILTER out of the box.Lightweight installation and smooth performance for large datasets.Free and user-friendly interface with powerful built-in data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the MATCH function count duplicate values multiple times?

The MATCH function evaluates each cell individually. If a value appears three times in your first column and exists in the second column, MATCH returns a successful hit for all three instances, inflating your total count.

How do I exclude blank cells when comparing two columns?

You can exclude blank cells by adding a condition within the FILTER function, such as (K4:K226<>""). This logical test forces the formula to ignore any empty rows in your specified range.

Can I count unique values in older versions of Excel?

Yes, but it requires much more complex array formulas combining SUM, IF, FREQUENCY, and MATCH. For simplicity, better readability, and faster performance, upgrading to a version that supports dynamic arrays (like Microsoft 365 or WPS Office) is highly recommended.

What does the COUNTIF segment of the formula do?

The COUNTIF(L4:L226,K4:K226)>0 segment checks if each item in column K exists in column L. It acts as a logical filter, returning TRUE only for items that have at least one corresponding match in the second column.

Excel & Data Problems