Stumped Ranking Question

[schielrn]

Ok I was working on another problem and came across something that has stumped me and I haven't been able to solve it for the past 30 minutes.

Is it possible to rank these WITHOUT the use of a helper column, VBA or a UDF, only native Excel Formulas:

Excel Workbook
	A	B	C	D	E	F	G	H
32	Store	Name	$ Spent	Helper Column			Changing D33 to sumifs	Changing D33 to sumifs
33	Store1	jake	64	64	6	6	6	6
34	Store1	jim	125	125	3	3	3	3
35	Store1	joe	73	101	4	4	4	4
36	Store1	john	63	126	1	1	1	1
37	Store1	joe	28	101	4	4	4	4
38	Store1	john	63	126	1	1	1	1
39	Store2	jake	79	79	2	2	2	2
40	Store2	jim	45	73	3	3	3	3
41	Store2	jim	28	73	3	3	3	3
42	Store2	joe	84	84	1	1	1	1
43	Store2	john	71	71	5	5	5	5
Sheet3

Cell Formulas
Range	Formula
D33	=SUMIFS($C$33:$C$43,$A$33:$A$43,A33,$B$33:$B$43,B33)
F33	=COUNTIFS($A$33:$A$43,A33,$D$33:$D$43," > "&D33)+1
H33	=COUNTIFS($A$33:$A$43,A33,$D$33:$D$43," > "&SUMIFS($C$33:$C$43,$A$33:$A$43,A33,$B$33:$B$43,B33))+1

#VALUE!

As you can see in F&G, I can do this with the use of the helper column, but would like to eliminate it. I can change the D33 single part to a sumifs and eliminate that, my problem is creating the array of values summed based on A&B, but collecting that in an array for ranking purposes.

I'm not sure if it is possible without the helper column, but just thought I'd ask.

 

[barry houdini]

Hello Ron,

Thanks, I didn't think to use SUMPRODUCT, the first formula I suggested could also be amended that way.

I don't think that the order of the data makes any difference to the results, though.

Formula three, for instance, gets different results by design from those suggested by Schielrn. It seems to me that if you are summing all the values for each name within each store then the ranking will apply to that name (within that store) not to a specific entry so, for store 1, for instance, of course John is ranked 1 but that's a rank for his combined score so, although it's shown twice, the next rank should be 2 so Jim will be ranked 2 according to my formula rather than 3 (and of course the other ranks will be affected).

List the same data in any other order and the formula should still give the same results, I think.

That method of ranking would appear to fit in with the expected results shown by dollygg in the linked question, although on a small set of data that's perhaps a tenuous assumption.

regards, barry

 

[schielrn]

It seems to me that if you are summing all the values for each name within each store then the ranking will apply to that name (within that store) not to a specific entry so, for store 1, for instance, of course John is ranked 1 but that's a rank for his combined score so, although it's shown twice, the next rank should be 2 so Jim will be ranked 2 according to my formula rather than 3 (and of course the other ranks will be affected).

This is theoretically what I was looking for and have put it to use in something I am using as well, when I started helping in the other thread.

I was happy with the results of 1,3,5 for ranking them as knowing that if they were duplicated numbers were skipped. I could live with that.

But my bossed loved what you did as far as 1,2,3 and not skipping numbers as there is a relationship between the combined scores and should be treated as one.

Thanks again to both of you.

 

[Ron Coderre]

Evidently, I had a "hardware problem": A loose nut in front of my keyboard :\

You're right...all three formulas work as advertised.
PLUS...The 3rd formula also returns the correct results when SUM is replaced by SUMPRODUCT:

E33: =SUMPRODUCT((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

 

[snolze123]

I see that this has been resolved for a while now, but I just came across this thread and thought I would share another solution:

E33: {=SUM(N($C33<=IF($A33=$A$33:$A$43,$C$33:$C$43,-1E+40)))}

It is an array formula but it's so compact and elegant that I love it! I honestly haven't spent the time to study exactly how it works, but it does lol.