How do I?
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? |
How do I?
=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? |
How do I?
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? |
How do I?
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)) |
How do I?
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? |
All times are GMT +1. The time now is 08:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com