How to Count Shared Values Only Once Across Two Excel Columns
Question details
The user needs a formula to count how many matching values exist between two distinct columns, ensuring that shared values are only counted once even if duplicates exist.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing lists of data extracted from separate PivotTables to determine the exact number of overlapping unique items.
- Observed behavior
- The formula needs to output a single integer representing the exact number of distinct shared items between the two ranges.
Ensure you are using a recent version of your spreadsheet software that supports dynamic array functions like FILTER and UNIQUE.
Use UNIQUE, FILTER, and COUNTA (For Columns with Duplicates)
This is the most robust method. It filters the first column for matches in the second column, removes any internal duplicates with UNIQUE, and counts the final list.
When dealing with data from PivotTables or combined datasets, it is common for the source lists themselves to contain duplicated values. Using the UNIQUE function guarantees each shared value is only counted once in your final result.
Click on an empty cell where you want the final count to be displayed.
Type `=COUNTA(UNIQUE(FILTER(A2:A6,COUNTIF(B2:B8,A2:A6)>0)))` into the formula bar.
Change `A2:A6` to match the data range of your first column, and `B2:B8` to match the data range of your second column.
Press Enter. The cell will now display the exact number of distinct shared values.
Use FILTER and COUNTA (For Columns Without Duplicates)
If you are certain that the first column contains strictly unique values, you can omit the UNIQUE function for a slightly simpler formula.
Easily Count and Analyze Complex Data with WPS Spreadsheet
WPS Office provides full support for advanced array formulas like UNIQUE and FILTER, allowing you to seamlessly process complex data, count overlaps, and analyze lists without needing complicated workarounds.
- 1. Open your data in WPS: Launch WPS Spreadsheet and open the workbook containing your two columns of data.
- 2. Apply the array formula: Select an empty cell and type the `=COUNTA(UNIQUE(FILTER(...)))` formula tailored to your specific ranges.
- 3. Get instant results: Press Enter to instantly see the deduplicated count of shared values across your data arrays.
Frequently Asked Questions
Why does my formula return a #NAME? error?
A #NAME? error typically occurs if you are using an older version of your spreadsheet software that does not support the FILTER or UNIQUE functions. You will need a modern version like Excel 2021, Microsoft 365, or the latest version of WPS Office.
Can I count shared values in older versions of Excel without UNIQUE or FILTER?
Yes. In older versions, you can use a combination of SUMPRODUCT and COUNTIF, such as `=SUMPRODUCT(--(COUNTIF(B2:B8, A2:A6)>0))`. However, if column A has internal duplicates, you may need more complex array formulas or a helper column to deduplicate it first.
How do I highlight these shared values instead of just counting them?
You can highlight shared values using Conditional Formatting. Select the data in your first column, navigate to Conditional Formatting > New Rule, choose to use a formula, and enter `=COUNTIF($B$2:$B$8, A2)>0`. Choose a fill color and apply.
Does this formula differentiate between text and numbers?
Yes, functions like COUNTIF and FILTER check for exact matches, so formatting matters. A number stored as text (e.g., '1') might not be recognized as a match for the actual integer (1). Ensure both columns share the same data type.
