|
Count repeats
In A1:E1, I have 5 bank names
In G1:K1, I have 5 more bank names In M1, I want to have a formula that counts the number of repeats between the two sets. (I would then copy this down the 200 rows). Example, A1:E1 has CSFB, CITI, DB, JPM, MS, and G1:K1 has MS, LB, BoFA, DB, CITI The answer in M1 would be 3 Thanks in advance for your help Daniel Bonallack |
Hi
try: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:E1,G1:K1,0)))) -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: In A1:E1, I have 5 bank names In G1:K1, I have 5 more bank names In M1, I want to have a formula that counts the number of repeats between the two sets. (I would then copy this down the 200 rows). Example, A1:E1 has CSFB, CITI, DB, JPM, MS, and G1:K1 has MS, LB, BoFA, DB, CITI The answer in M1 would be 3 Thanks in advance for your help Daniel Bonallack |
Thank you, that works, though I don't really understand the formula.
Would you mind a follow-up question? What if the banks were not A1:E1, but on A1, C1, E1, G1, H1 (and their counterparts on every second cell starting at K1) How would this change the formula? Also, could you tell me why you have two minus signs before the "ISNUMBER" component of the formula (and why is ISNUMBER used, when I'm matching text). Thanks, hope you (or anyone else) can answer this for me. regards Daniel "Frank Kabel" wrote: Hi try: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:E1,G1:K1,0)))) -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: In A1:E1, I have 5 bank names In G1:K1, I have 5 more bank names In M1, I want to have a formula that counts the number of repeats between the two sets. (I would then copy this down the 200 rows). Example, A1:E1 has CSFB, CITI, DB, JPM, MS, and G1:K1 has MS, LB, BoFA, DB, CITI The answer in M1 would be 3 Thanks in advance for your help Daniel Bonallack |
Hi
what is in the other cells?. For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: Thank you, that works, though I don't really understand the formula. Would you mind a follow-up question? What if the banks were not A1:E1, but on A1, C1, E1, G1, H1 (and their counterparts on every second cell starting at K1) How would this change the formula? Also, could you tell me why you have two minus signs before the "ISNUMBER" component of the formula (and why is ISNUMBER used, when I'm matching text). Thanks, hope you (or anyone else) can answer this for me. regards Daniel "Frank Kabel" wrote: Hi try: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:E1,G1:K1,0)))) -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: In A1:E1, I have 5 bank names In G1:K1, I have 5 more bank names In M1, I want to have a formula that counts the number of repeats between the two sets. (I would then copy this down the 200 rows). Example, A1:E1 has CSFB, CITI, DB, JPM, MS, and G1:K1 has MS, LB, BoFA, DB, CITI The answer in M1 would be 3 Thanks in advance for your help Daniel Bonallack |
Hi Frank
Thanks for the link, I'll look into that. With regard to the cell range change, I would just extend the range of your formula, but the problem is that the inbetween cells also contain bank names. I want to check A1, C1, E1, etc against L1, N1, P1 etc, and then B1, D1, etc against M1, O1, etc. Thanks again regards Daniel "Frank Kabel" wrote: Hi what is in the other cells?. For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: Thank you, that works, though I don't really understand the formula. Would you mind a follow-up question? What if the banks were not A1:E1, but on A1, C1, E1, G1, H1 (and their counterparts on every second cell starting at K1) How would this change the formula? Also, could you tell me why you have two minus signs before the "ISNUMBER" component of the formula (and why is ISNUMBER used, when I'm matching text). Thanks, hope you (or anyone else) can answer this for me. regards Daniel "Frank Kabel" wrote: Hi try: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:E1,G1:K1,0)))) -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: In A1:E1, I have 5 bank names In G1:K1, I have 5 more bank names In M1, I want to have a formula that counts the number of repeats between the two sets. (I would then copy this down the 200 rows). Example, A1:E1 has CSFB, CITI, DB, JPM, MS, and G1:K1 has MS, LB, BoFA, DB, CITI The answer in M1 would be 3 Thanks in advance for your help Daniel Bonallack |
Daniel,
In addition to the link that Frank posted, you will have problems with non-contiguous ranges, as the functions work on contiguous ranges. Also the ISNUMBER is used as MATCH will return an index number if matched, or #N/A if the item in the first range is not found in the second range, and so ISNUMBER is used to return TRUE/FALSE values which the -- coerces into 1/0 which SUMPRODUCT adds up.The link explains the various arithmetic operators that will coerce the Booleans into 1/0s. -- HTH RP (remove nothere from the email address if mailing direct) "Daniel Bonallack" wrote in message ... Thank you, that works, though I don't really understand the formula. Would you mind a follow-up question? What if the banks were not A1:E1, but on A1, C1, E1, G1, H1 (and their counterparts on every second cell starting at K1) How would this change the formula? Also, could you tell me why you have two minus signs before the "ISNUMBER" component of the formula (and why is ISNUMBER used, when I'm matching text). Thanks, hope you (or anyone else) can answer this for me. regards Daniel "Frank Kabel" wrote: Hi try: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:E1,G1:K1,0)))) -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: In A1:E1, I have 5 bank names In G1:K1, I have 5 more bank names In M1, I want to have a formula that counts the number of repeats between the two sets. (I would then copy this down the 200 rows). Example, A1:E1 has CSFB, CITI, DB, JPM, MS, and G1:K1 has MS, LB, BoFA, DB, CITI The answer in M1 would be 3 Thanks in advance for your help Daniel Bonallack |
Hi
this would make the formula much more complicated :-) Any chance at least the second range is contingenous? -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: Hi Frank Thanks for the link, I'll look into that. With regard to the cell range change, I would just extend the range of your formula, but the problem is that the inbetween cells also contain bank names. I want to check A1, C1, E1, etc against L1, N1, P1 etc, and then B1, D1, etc against M1, O1, etc. Thanks again regards Daniel "Frank Kabel" wrote: Hi what is in the other cells?. For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: Thank you, that works, though I don't really understand the formula. Would you mind a follow-up question? What if the banks were not A1:E1, but on A1, C1, E1, G1, H1 (and their counterparts on every second cell starting at K1) How would this change the formula? Also, could you tell me why you have two minus signs before the "ISNUMBER" component of the formula (and why is ISNUMBER used, when I'm matching text). Thanks, hope you (or anyone else) can answer this for me. regards Daniel "Frank Kabel" wrote: Hi try: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:E1,G1:K1,0)))) -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: In A1:E1, I have 5 bank names In G1:K1, I have 5 more bank names In M1, I want to have a formula that counts the number of repeats between the two sets. (I would then copy this down the 200 rows). Example, A1:E1 has CSFB, CITI, DB, JPM, MS, and G1:K1 has MS, LB, BoFA, DB, CITI The answer in M1 would be 3 Thanks in advance for your help Daniel Bonallack |
Hi Frank
I could change the set up if necessary to suit the formula, but the current layout was requested by my boss - I could get the results then turn to values I suppose... The actual situation is that I have two magazines (Mag A and Mag B) who rank banks based on different categories. I am looking at two years - 2004 and 2003, and I want to see how many are in the top 5 in both years So: - in column A I have the various categories we're looking at. - In B1, D1, F1 etc I have the 1,2,3... ranked bank according to Mag A (2004) - In C1, E1, G1 etc I have the 1,2,3... ranked bank according to Mag B (2004) The range tested against (off to the right) is exactly the same, but for 2003. Does this help? By the way, the link you sent me to was great - I've read up on SUMPRODUCT, and forwarded it to my colleagues - thanks a lot. thanks Daniel "Frank Kabel" wrote: Hi this would make the formula much more complicated :-) Any chance at least the second range is contingenous? -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: Hi Frank Thanks for the link, I'll look into that. With regard to the cell range change, I would just extend the range of your formula, but the problem is that the inbetween cells also contain bank names. I want to check A1, C1, E1, etc against L1, N1, P1 etc, and then B1, D1, etc against M1, O1, etc. Thanks again regards Daniel "Frank Kabel" wrote: Hi what is in the other cells?. For an explanation see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: Thank you, that works, though I don't really understand the formula. Would you mind a follow-up question? What if the banks were not A1:E1, but on A1, C1, E1, G1, H1 (and their counterparts on every second cell starting at K1) How would this change the formula? Also, could you tell me why you have two minus signs before the "ISNUMBER" component of the formula (and why is ISNUMBER used, when I'm matching text). Thanks, hope you (or anyone else) can answer this for me. regards Daniel "Frank Kabel" wrote: Hi try: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:E1,G1:K1,0)))) -- Regards Frank Kabel Frankfurt, Germany Daniel Bonallack wrote: In A1:E1, I have 5 bank names In G1:K1, I have 5 more bank names In M1, I want to have a formula that counts the number of repeats between the two sets. (I would then copy this down the 200 rows). Example, A1:E1 has CSFB, CITI, DB, JPM, MS, and G1:K1 has MS, LB, BoFA, DB, CITI The answer in M1 would be 3 Thanks in advance for your help Daniel Bonallack |
| All times are GMT +1. The time now is 03:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com