Question: How many common cell values are there in list 1 and 2?

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

count-common-values.xls
(Excel 97-2003 Workbook *.xls)

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

How to find common values from two lists