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