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

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 values

This post demonstrates how to calculate unique distinct products (Column E) and meeting a criterion or multiple criteria. Table of Contents How many unique distinct products did Salesperson Jennifer sell? How many unique distinct products did Jennifer sell in January? […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with a criterion Find closest value Question: How to find closest number in a list? Answer: Array formula in cell C2: […]

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from the list A1:A11 in C1. How do I find those summed numbers in C1? I am going to use Excel […]

### Duplicate values

Table of contents Extract a list of duplicates from a column using array formula Extract duplicates using conditions Extract duplicates with exceptions Extract a list of duplicates from a column using array formula Excel 2007 array formula in C2: =INDEX($A$2:$A$20, […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it the dates are overlapping as well. Is there anyway to work this out? eg Serial | Start date | End […]

The array formula in this blog article has no "Fuzzy logic" nor vlookup function. But it can return names or words arranged differently and with minor misspellings just like a user defined function with "Fuzzy logic". There are too many […]

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

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