ExcelBanter - Comparing Cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Comparing Cells (https://www.excelbanter.com/excel-discussion-misc-queries/240290-comparing-cells.html)

I would like to compare the following and end up with the result on the
bottom. Can anyone help?
Column A Column B
1 3
3 3
2 4
5 5
I am looking for two different formula's to give me the following:

1. How many numbers in column B are an exact match to column A (Same number
& same row) (In this example the answer would be 2)

2. How many numbers in column B are the same as in column A but not in the
correct order (In this example the answer would be 0)

Hi,

The answer to you first question is this

=SUMPRODUCT(--(A1:A4=B1:B4))

I'm afraid I can't understand the second.

Mike

"basic" wrote:

I would like to compare the following and end up with the result on the
bottom. Can anyone help?
Column A Column B
1 3
3 3
2 4
5 5
I am looking for two different formula's to give me the following:

1. How many numbers in column B are an exact match to column A (Same number
& same row) (In this example the answer would be 2)

2. How many numbers in column B are the same as in column A but not in the
correct order (In this example the answer would be 0)