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

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

Compare two columns for same values [Excel Formula]

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Filter common values from three columns

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]

Compare two tables: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

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 […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

### 2 Responses to “How many common cell values are there in list 1 and 2?”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.