## Count identical numbers in two columns but items must be in same row

*Article updated on August 19, 2017*

Hi Oscar,Need a formula to count identical numbers in two columns but items must be in same row (position).

12 15

8 8 good count 1

22 19

7 22 for 22 not count cause is not in same row

14 14 good count 2

Array formula in cell E2:

**How to enter an array formula**

- Select cell E2
- Paste the formula in formula bar
- Press and hold CTRL + SHIFT simultaneously
- Press Enter
- Release all keys

Your formula now begins and ends with a curly bracket, if you did it right.

Like this {=SUM((A2:A6=B2:B6)*1)}

### Explaining formula

**Step 1 - Compare values in column A with column B**

A2:A6=B2:B6

becomes

{12;8;22;7;14}={15;8;19;22;14}

and returns

{FALSE;TRUE;FALSE;FALSE;TRUE}

**Step 2 - Multiply boolean values with 1**

To be able to sum the values in this array {FALSE;TRUE;FALSE;FALSE;TRUE} we need to convert them to 0 and 1. FALSE = 0 and TRUE = 1.

(A2:A6=B2:B6)*1

becomes

({FALSE;TRUE;FALSE;FALSE;TRUE})*1

and returns

{0;1;0;0;1}

**Step 3 - Sum values in array**

SUM((A2:A6=B2:B6)*1)

becomes

SUM({0;1;0;0;1})

and returns 2.

This formula also works with text values, to remove blanks before and after use TRIM function.

### Download excel *.xlsx file

Count identical numbers in two columns but items must be in same row.xlsx

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 […]Count how many numbers between a specific value occurring multiple times

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]### 3 Responses to “Count identical numbers in two columns but items must be in same row”

### 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 can use non array formula, like this:

=SUMPRODUCT((A2:A6=B2:B6)*(A2:A6/B2:B6))

Try to find another non array formula.

There is a number of ways to make such a count as non array formula, e.g. =SUM(INDEX((A2:A6=B2:B6)*1,,)). But why not use array formula if it does the job?

[quote]Leonid says:

April 12, 2016 at 6:49 pm

There is a number of ways to make such a count as non array formula, e.g. =SUM(INDEX((A2:A6=B2:B6)*1,,)). But why not use array formula if it does the job?[/quote]

I suspect that Kidd has many more rows and columns in his file and CSE formulas are eating resources.