Number Match Formula?
 

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

Number Match Formula?
 

On Jun 16, 8:15*am, JAgger1 wrote:
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.

Number Match Formula?
 

On Jun 16, 1:18*pm, JAgger1 wrote:
On Jun 16, 8:15*am, JAgger1 wrote:

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.

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.

Number Match Formula?
 

On 16/06/2011 15:08, trip_to_tokyo wrote:
On Jun 16, 1:18 pm, wrote:
On Jun 16, 8:15 am, wrote:

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.

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

Number Match Formula?
 

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.

Number Match Formula?
 

On Jun 16, 7:46*am, JAgger1 wrote:
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 ;-).

On Jun 16, 5:18 am, JAgger1 wrote:
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))

Number Match Formula?
 

On 16/06/2011 23:35, joeu2004 wrote:
On Jun 16, 7:46 am, wrote:
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 ;-).

On Jun 16, 5:18 am, wrote:
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))

ITYM

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

Entered as an array formula Ctrl-Shift-Enter

Regards,
Martin Brown

Number Match Formula?
 

On Jun 17, 4:09*am, Martin Brown
wrote:
On 16/06/2011 23:35, joeu2004 wrote: On Jun 16, 7:46 am, *wrote:
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 ;-).

On Jun 16, 5:18 am, *wrote:
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))

ITYM

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

Entered as an array formula Ctrl-Shift-Enter

Regards,
Martin Brown

Perfect! Thanks