Number Match Formula?

[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

 

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

 

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

 

[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

 

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

 

[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))

 

[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

 

[JAgger1]

ITYM

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

Entered as an array formula Ctrl-Shift-Enter

Regards,
Martin Brown

Perfect! Thanks