Count repeats

[Guest]

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

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:E1,G1:K1,0))))

 

[Guest]

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]

Hi
what is in the other cells?.
For an explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Frank Kabel]

Hi
this would make the formula much more complicated
Any chance at least the second range is contingenous?

 

[Guest]

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