Cells d101 thru l101 have values say 4 4 5 6 7 2 3 3 7 and cells c17 thru k17
have values say 4 5 7 6 6 3 3 4 6. How
do I return a value of 3 since there are 3 matches between the two ranges?
What formula can I use without getting too involved?

=SUMPRODUCT((D101:I101=C17:K17)*1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bert" wrote:

Cells d101 thru l101 have values say 4 4 5 6 7 2 3 3 7 and cells c17 thru k17
have values say 4 5 7 6 6 3 3 4 6. How
do I return a value of 3 since there are 3 matches between the two ranges?
What formula can I use without getting too involved?

Try

=SUMPRODUCT(--(C17:K17=D101:L101))

If this post helps click Yes
---------------
Jacob Skaria


"Bert" wrote:

Cells d101 thru l101 have values say 4 4 5 6 7 2 3 3 7 and cells c17 thru k17
have values say 4 5 7 6 6 3 3 4 6. How
do I return a value of 3 since there are 3 matches between the two ranges?
What formula can I use without getting too involved?

Bert wrote:
Cells d101 thru l101 have values say 4 4 5 6 7 2 3 3 7 and cells c17 thru k17
have values say 4 5 7 6 6 3 3 4 6. How
do I return a value of 3 since there are 3 matches between the two ranges?
What formula can I use without getting too involved?


Array formula (commit with CTRL+SHIFT+ENTER):

=SUM(--(D101:L101=C17:K17))

Bert,

It looks like this should work for you.

=SUMPRODUCT((C1:K1=D101:L101)*(1))

HTH,

Conan Kelly





"Bert" wrote in message
...
Cells d101 thru l101 have values say 4 4 5 6 7 2 3 3 7 and cells c17 thru
k17
have values say 4 5 7 6 6 3 3 4 6.
How
do I return a value of 3 since there are 3 matches between the two ranges?
What formula can I use without getting too involved?