Stumped Ranking Question

Using your posted example....Try this:

This ARRAY FORMULA, completed by pressing CTRL+SHIFT+ENTER (instead of just ENTER) returns the relative rank of the Name's cumulative $ Spent relative to the cumulative totals of other Names from the same store:
Copy E33 and paste into E34:E43

In the above example, these are the returned values:
E33: 6
E34: 3
E35: 4
E36: 1
E37: 4
E38: 1
E39: 2
E40: 3
E41: 3
E42: 1
E43: 5

Is that something you can work with?

Ron,

very nice. out of curiosity, what is the INDEX part of the formula doing?

Since nobody I work with *ever* remembers to use CTRL+SHIFT+ENTER on array formulas, I avoid them whenever possible. I can often convert what would be an array-formula to a regular formula via the strategic insertion of INDEX functions. That would have been the case in this formula...but I couldn't avoid the C+S+E (and forgot to remove the INDEX function)

Consequently the formula should have been:

Actually...There *is* a way to avoid the C+S+E and use a regular formula!

Thank you very much I was close, but didn't think to use index, which is where I was getting thrown off trying to use an if statement.

This appears exactly what I have been trying to solve.

I put it to use here and it worked greatly:

http://www.mrexcel.com/forum/showthread.php?t=529558

I also made sure to reference you as I didn't want to plagarize.

very nice. and I learned something about the ability to avoid the C+S+E for sheets used by other people.

thanks Ron

Ron Coderre said:

Actually...There *is* a way to avoid the C+S+E and use a regular formula!

Code:

E33: =MATCH(SUMIFS($C$33:$C$43,$A$33:$A$43,A33,$B$33:$B$43,B33),INDEX(LARGE(
INDEX(SUMIFS($C$33:$C$43,$A$33:$A$43,$A$33:$A$43,$B$33:$B$43,$B$33:$B$43)
*($A$33:$A$43=A33),0),INDEX(ROW(INDIRECT("1:"&COUNTIF($A$33:$A$43,A33))),0)
),0),0)

Click to expand...

I posted a reply to the original question but made the assumption there that column B values would only repeat against the same column A value (e.g. for the examples here that John exists on a row with store 1 he couldn't also be on a store 2 row)

If that's the case and you rank as here then you could use this formula with CSE

=SUM((SUMIFS(C$33:C$43,B$33:B$43,B$33:B$43,A$33:A$43,A33)>SUMIFS(C$33:C$43,B$33:B$43,B33,A$33:A$43,A33))+0)+1

but for the equivalent of Ron's formula, allowing for names to repeat against any store this version will work

=SUM((SUMIFS(C$33:C$43,B$33:B$43,B$33:B$43,A$33:A$43,A$33:A$43)*(A$33:A$43=A33)>SUMIFS(C$33:C$43,B$33:B$43,B33,A$33:A$43,A33))+0)+1

but I would consider that as you are ranking the name within that store the second placed name shouldn't be ranked 3 just because the first place name appears twice (there should only be ranking gaps if different names are tied) so making that assumption you can add another element, i.e.

=SUM((SUMIFS(C$33:C$43,B$33:B$43,B$33:B$43,A$33:A$43,A$33:A$43)*(A$33:A$43=A33)*(MATCH($A$33:$A$43&$B$33:$B$43,$A$33:$A$43&$B$33:$B$43,0)=ROW(A$33:A$43)-ROW(A$33)+1)>SUMIFS(C$33:C$43,B$33:B$43,B33,A$33:A$43,A33))+0)+1

Interesting...
Change the SUM to SUMPRODUCT in your second formula...and you don't need to C+S+E it...which I like:

However, the 1st and 3rd formulas do require C+S+E and only work if the rows for individaual stores are grouped together. Whereas, the longer formulas I posted work for any order of stores. Either one may be preferable, depending on the certainty that the data will, or may not, be sorted.