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.

Channel	Category	Week	Units	Sales	Rank
Grocery	Apple	1	7	$18.00	2
Grocery	Apple	1	7	$21.00	1
Grocery	Apple	2	2	$6.00	1
Grocery	Orange	2	5	$15.00	1
Home	Apple	1	3	$9.00	2
Home	Apple	1	9	$27.00	1
Home	Apple	2	3	$9.00	1
Restaurant	Orange	2	5	$15.00	2
Restaurant	Orange	2	7	$21.00	1
Restaurant	Orange	2	1	$3.00	3

<tbody>
</tbody>

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
1	Restaurant	People there	Rank	Total	Rank			Restaurant
2	Italian	20	1	90	1			Italian	90
3	Greek	17	5	17	5			Chinese	75
4	Sushi	40	3	40	3			Sushi	40
5	Chinese	75	2	75	2			Soup	30
6	Italian	35						Greek	17
7	Burger	17	5	17	5			Burger	17
8	Soup	30	4	30	4			Mexican	15
9	Mexican	15	7	15	7			0	0
10	Italian	35							0
11
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula I2 =SUMIF($A$2:$A$20,H2,$B$2:$B$20) D2 =IF(COUNTIF($A$2:$A2,A2)>1,"",SUMIF($A$2:$A$20,A2,$B$2:$B$20)) E2 =IFERROR(RANK(D2,$D$2:$D$20),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula H2 {=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)),"")} C2 {=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))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.