Rank based on Multiple criteria (Like a RankIFS)

[calecote]

Hi,


I've read a lot of the forums on who to use rank based on multiple criteria, but I need to rank criteria based on multiple criteria.

I tried using something like this: =COUNTIF($B26:$T26,">"&N26)+1+SUMPRODUCT(--($B26:$T26=N26),--($B27:$T27>N27)) but it doesnt work for what I'm looking to do.

[TABLE="width: 242"]
<tbody>[TR]
[TD]Channel[/TD]
[TD]Category[/TD]
[TD]Week[/TD]
[TD]Units[/TD]
[TD]Sales[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Grocery[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]$18.00[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Grocery[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]$21.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Grocery[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]$6.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Grocery[/TD]
[TD]Orange[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]$15.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]$9.00[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]$27.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]$9.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Restaurant[/TD]
[TD]Orange[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]$15.00[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Restaurant[/TD]
[TD]Orange[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]$21.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Restaurant[/TD]
[TD]Orange[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]$3.00[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I want to create one formula I can drag down that chooses the most units than the most sales based on the criteria of Channel, category and week.

Thanks,

Cale

 

[Eric W]

A Pivot Table is a good option, and pretty easy to run, once you've seen it.

If you're still looking for a formula, you didn't really say how you wanted the output to look. Here are a few possibilities:

	A	B	C	D	E	F	G	H	I
Rank	Total	Rank	Restaurant
Italian
Chinese
Sushi
Soup
			Greek
Burger
Mexican

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Restaurant[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]People there[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Italian[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]90[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Greek[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]75[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Sushi[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]40[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]40[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Chinese[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]75[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Italian[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]35[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Burger[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Soup[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Mexican[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Italian[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]35[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=SUMIF($A$2:$A$20,H2,$B$2:$B$20)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(COUNTIF($A$2:$A2,A2)>1,"",SUMIF($A$2:$A$20,A2,$B$2:$B$20))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IFERROR(RANK(D2,$D$2:$D$20),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$20,MATCH(MAX(IF(ISERROR(MATCH($A$2:$A$20,$H$1:$H1,0)),SUMIF($A$2:$A$20,$A$2:$A$20,$B$2:$B$20),-1)),IF(ISERROR(MATCH($A$2:$A$20,$H$1:$H1,0)),SUMIF($A$2:$A$20,$A$2:$A$20,$B$2:$B$20),-2),0)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IF(A2="","",IF(COUNTIF($A$2:$A2,A2)>1,"",SUM(IF((SUMIF($A$2:$A$20,A2,$B$2:$B$20)<SUMIF($A$2:$A$20,$A$2:$A$20,$B$2:$B$20))*(IF($A$2:$A$20="",0,MATCH($A$2:$A$20,$A$2:$A$20,0)=ROW($A$2:$A$20)-ROW($A$2)+1)),1))+1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Column C is a formula that creates the ranking, without needing a helper column. Duplicate entries are blank. Columns D and E achieve the same thing using 2 columns, but the formulas are far simpler.

Column H is a helper free formula that ranks the restaurants by people. Column I is just a SUMIF to get the matching count.

I seem to remember doing something very similar a long time ago a bit more elegantly. I'll see if I can dig it up.

 

[calecote]

The data is going to be posted to the bottom and a Pivot table would work, but I was hoping there was a single formula that could be inputted next to the sata that essentially had the ability to cumulitively sum. If not I would just build a separate sumif table.

 

[edoardo17]

I am having a similar problem to the above. I have a list of Couriers and the time and day that they have delivered the goods. I am trying to establish which order was delivered first using Rank.

https://docs.google.com/spreadsheets/d/1ObfJ0LNu_6lUiZRIdHXL3r27axg2zueuw0KMXzQq160/edit?usp=sharing

However, Google Sheets keeps returning an Error.
Would love some help

 

[Eric W]

Welcome to the board.

For future reference, it's probably best to open a new thread with a new question, and not add to a very old thread. You'll get more eyes on it.

Secondly, I'm trying to figure out exactly what you want. I see a list of names and times. Some of the names are repeated. How exactly do you want to rank them? If it's by lowest average delivery time, you'll need a start and an end time for each delivery. If you just want a rank of most deliveries, that's possible, although it would be easier to use a pivot table (which I can explain if you want). If you want a ranking by some other method, let me know.