How many common cell values are there in list 1 and 2?
Question: How many common cell values are there in list 1 and 2?
Answer:
Here is how to count matching entries in two columns.
Array formula in cell E2:
=COUNT(MATCH(A2:A17;B2:B17;0)) + Ctrl + Shift + Enter
Explaining array formula
=COUNT(MATCH(A2:A17;B2:B17;0))
Step 1 - Find common values
=COUNT(MATCH(A2:A17;B2:B17;0))
MATCH(A2:A17,B2:B17,0) returns the position of a match in an array.
MATCH(A2:A17,B2:B17,0)
becomes
{#N/A, #N/A, #N/A, #N/A, #N/A, #N/A, #N/A, 1, 2, 3, 4, #N/A, #N/A, #N/A, #N/A, #N/A, #N/A}
Step 2 - Count numbers
=COUNT(MATCH(A2:A17;B2:B17;0))
becomes
=COUNT({#N/A, #N/A, #N/A, #N/A, #N/A, #N/A, #N/A, 1, 2, 3, 4, #N/A, #N/A, #N/A, #N/A, #N/A, #N/A})
and returns 4
Download excel sample file for this tutorial.
count-common-values.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value
COUNT(value_1, value_2)
Counts the number of cells in a range that contains numbers
Related posts:
Excel udf: Filter common values between two cell ranges in excel
Filter common values from three columns in excel
Most common value between two dates in a range in excel
Filter common values between two ranges using array formula in excel
Recommended categories
You have an excellent site!
When I use =SUM(IF(NOT(ISERROR(MATCH(A2:A17,B2:B17,0))),1,0))
the common values column shows 4, 3, 2, 1, 0 0 0 0 not any letters
Your download excel sample file uses
=INDEX(A2:A17,SMALL(IF(NOT(ISERROR(MATCH(A2:A17,B2:B17,0))),(ROW(2:17)-1),""),ROW()-1))
When paste and copy down this formula I get
GG
II
#ref
#ref
#num
What's up?
Andy,
I think this blog posts counts the number of common values between List 1 and List 2.
If you open the attached file, cell E2 contains this array formula:
Edit: I have changed this post,formula and the attached file.