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

*Article updated on March 06, 2011*

**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

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]### 2 Responses to “How many common cell values are there in list 1 and 2?”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.