schielrn
Well-known Member
- Joined
- Apr 4, 2007
- Messages
- 6,941
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:
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.
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.
Last edited: