Number Match Formula?

  • Thread starter Thread starter JAgger1
  • Start date Start date
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
 
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
Click to expand...

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.
 
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.
Click to expand...

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.
 
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.
Click to expand...

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
 
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.
 
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.
Click to expand...

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.
Click to expand...

=SUMPRODUCT(--(COUNTIF(A1:A1000,B1:B30)>0))
 
I'm sorry, but that is exactly what you said the first time -- well,
the second time ;-).



=SUMPRODUCT(--(COUNTIF(A1:A1000,B1:B30)>0))
Click to expand...
ITYM

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

Entered as an array formula Ctrl-Shift-Enter

Regards,
Martin Brown
 
Back
Top