# Number Match Formula?

J

#### JAgger1

Hi all

I've got two sets of numbers:

a: 1 3 4 7 9 11 13 15 16 18 21 22 25 32 33 36 38 39 41 42 43 44 46 48
49 50
b: 2 3 5 8 9 10 13 16 17 18 20 21 29 30 31 32 35 39 40 45 48 49 50 52
54 55

How would I write a formula that would tell me how many numbers match
from each set? Thanks

J

#### JAgger1

Hi all

I've got two sets of numbers:

a: 1 3 4 7 9 11 13 15 16 18 21 22 25 32 33 36 38 39 41 42 43 44 46 48
49 50
b: 2 3 5 8 9 10 13 16 17 18 20 21 29 30 31 32 35 39 40 45 48 49 50 52
54 55

How would I write a formula that would tell me how many numbers match
from each set? Thanks

Sorry, I should have said, how would I write a formula that would tell
me how many numbers from set B match numbers from set A.

T

#### trip_to_tokyo

Sorry, I should have said, how would I write a formula that would tell
me how many numbers from set B match numbers from set A.

EXCEL 2007
Assuming that the 2 sets of numbers that you have given are in 2
columns (A and B starting at row 1).

In cell C1 type:-
=IF(A1=B1,"yes","no")

Copy and paste the above formula down to and including cell C24.

In cell C25 type:-
=COUNTIF(C1:C24,"yes")

Answer in cell C25 is 4.

M

#### Martin Brown

EXCEL 2007
Assuming that the 2 sets of numbers that you have given are in 2
columns (A and B starting at row 1).

In cell C1 type:-
=IF(A1=B1,"yes","no")

Copy and paste the above formula down to and including cell C24.

In cell C25 type:-
=COUNTIF(C1:C24,"yes")

Answer in cell C25 is 4.

Looks to me by inspection that the right answer is 11.

And something like with the numbers in rows 1 & 3 starting column A
Use the match formula in row 5 and then count numeric answers
=MATCH(A3,\$A1:\$Z1,0)
=COUNTIF(A5:Z5,">0")

It does look a bit like homework too.

There might well be a cute way to do it with a single array formula.

Regards,
Martin Brown

J

#### JAgger1

Sorry, my bad. I should have said how many numbers from set B occur in
Set A, not which numbers match in value and postion.

J

#### joeu2004

Sorry, my bad. I should have said how many numbers from
set B occur in Set A, not which numbers match in value
and postion.

I'm sorry, but that is exactly what you said the first time -- well,
the second time ;-).

Sorry, I should have said, how would I write a formula
that would tell me how many numbers from set B match
numbers from set A.

=SUMPRODUCT(--(COUNTIF(A1:A1000,B1:B30)>0))

M

#### Martin Brown

I'm sorry, but that is exactly what you said the first time -- well,
the second time ;-).

=SUMPRODUCT(--(COUNTIF(A1:A1000,B1:B30)>0))
ITYM

=SUM(COUNTIF(A1:Z1,A3:Z3))

Entered as an array formula Ctrl-Shift-Enter

Regards,
Martin Brown

J

#### JAgger1

ITYM

=SUM(COUNTIF(A1:Z1,A3:Z3))

Entered as an array formula Ctrl-Shift-Enter

Regards,
Martin Brown

Perfect! Thanks