hope i can get some help on this one
looking for a formula that looks into a group of numbers (8) and then
looks into another group of numbers (8) and returns a value if if there
are any matchs.

example
a b c d e
1 3 7 1 5 0
2 2 6 3 4 8 "result=1"
3
4
5

thanks in advance

Hi!

What result would you expect if these were the numbers:

a b c d e
1 3 7 1 8 0
2 3 6 3 4 8

Biff

"bob" wrote in message
oups.com...
hope i can get some help on this one
looking for a formula that looks into a group of numbers (8) and then
looks into another group of numbers (8) and returns a value if if there
are any matchs.

example
a b c d e
1 3 7 1 5 0
2 2 6 3 4 8 "result=1"
3
4
5

thanks in advance

thanks Biff
ok i want to count the number of times a matching number appears in
row1 from a group of numbers in row2 so i would expect the result of
your question to be "3". i can do a countif (a1,a2:e2) in cell W1 then
do a countif(b1,a2:e2) in cell X1etc.but i have a group of 8 cells to
try and match and was hoping there would be an better way to do it in
just one cell for all 8 cells

something like =countif (a1:e1, anymatchs a2:e2) but i know thats not
it
thanks in advance

bob


Biff wrote:
Hi!

What result would you expect if these were the numbers:

a b c d e
1 3 7 1 8 0
2 3 6 3 4 8

Biff

"bob" wrote in message
oups.com...
hope i can get some help on this one
looking for a formula that looks into a group of numbers (8) and then
looks into another group of numbers (8) and returns a value if if there
are any matchs.

example
a b c d e
1 3 7 1 5 0
2 2 6 3 4 8 "result=1"
3
4
5

thanks in advance

Try this:

=SUMPRODUCT(COUNTIF(A1:E1,A2:E2))

Biff

"bob" wrote in message
oups.com...
thanks Biff
ok i want to count the number of times a matching number appears in
row1 from a group of numbers in row2 so i would expect the result of
your question to be "3". i can do a countif (a1,a2:e2) in cell W1 then
do a countif(b1,a2:e2) in cell X1etc.but i have a group of 8 cells to
try and match and was hoping there would be an better way to do it in
just one cell for all 8 cells

something like =countif (a1:e1, anymatchs a2:e2) but i know thats not
it
thanks in advance

bob


Biff wrote:
Hi!

What result would you expect if these were the numbers:

a b c d e
1 3 7 1 8 0
2 3 6 3 4 8

Biff

"bob" wrote in message
oups.com...
hope i can get some help on this one
looking for a formula that looks into a group of numbers (8) and then
looks into another group of numbers (8) and returns a value if if there
are any matchs.

example
a b c d e
1 3 7 1 5 0
2 2 6 3 4 8 "result=1"
3
4
5

thanks in advance

I think you need something like:

=SUM(--(COUNTIF(A1:E1,A2:E2)<0))

entered as array formula (i.e. confirmed with Shift+Ctrl+Enter). The
philosophy of this formula is that it goes over each element in row 2
and asks if the element appears at least once.

Is it what you want?
Kostis Vezerides

Try also:
=SUMPRODUCT(--ISNUMBER(MATCH(A2:E2,A1:E1,0)))
Adapt to suit for an 8 cells stretch ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

thanks much max works like a charm..for some reason the other two did
not but thanks to all for trying will perhaps use those as needs
require

bob

Max wrote:
Try also:
=SUMPRODUCT(--ISNUMBER(MATCH(A2:E2,A1:E1,0)))
Adapt to suit for an 8 cells stretch ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---