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]

Try:

	A	B	C	D	E	F
1	Channel	Category	Week	Units	Sales	Rank
2	Grocery	Apple	1	7	$18.00	1
3	Grocery	Apple	1	7	$21.00	1
4	Grocery	Apple	2	2	$6.00	1
5	Grocery	Orange	2	5	$15.00	1
6	Home	Apple	1	3	$9.00	2
7	Home	Apple	1	9	$27.00	1
8	Home	Apple	2	3	$9.00	1
9	Restaurant	Orange	2	5	$15.00	2
10	Restaurant	Orange	2	7	$21.00	1
11	Restaurant	Orange	2	1	$3.00	3

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

Sheet1​

Worksheet Formulas Cell Formula F2 =COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,">"&D2)+1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[calecote]

Hey Eric,

Thanks so much for the help. The formula looks great, but unfortunately I want the formula to first rank on units and then if there is a tie rank based on Sales.

Other than that the formula does work though!

Thanks,

Cale

 

[Eric W]

In that case, try:

=SUMPRODUCT(--($A$2:$A$11=A2),--($B$2:$B$11=B2),--($C$2:$C$11=C2),--($D$2:$D$11*100000+$E$2:$E$11>D2*100000+E2))+1

You shouldn't use whole column references here, since SUMPRODUCT isn't aware of the end of range like COUNTIFS is. Use a specific row for the max. The 100000 in the formula (twice) should be a value greater than your largest sales amount.

Good luck!

 

[calecote]

Hi Eric,

Wen I do that it brings back an #N/A. I updated the formula to be reflective of my sheet:

=SUMPRODUCT(--($L$2:$L$15378=L3),--($M$2:$M$15378=M3),--($N$2:$N$15378=N3),--($G$2:$G$15378*100000+$K$2:$K$15378>G3*100000+K3))+1

L= Channel
M= Category
N=Week Number
G= Unit Sales
K= Gross Margin

I tried to look by evaluating the formula, but the sumproduct is too large for me to know where the error occurs.

Thanks for all the help!

Cale

 

[Eric W]

The SUMPRODUCT may look large, but it's not that bad. Each interior set of parentheses is just one condition. The long one at the end is creating a combined condition using the units and sales.

I took your formula exactly as you wrote it, and inserted it into my sheet, and it worked. My assumption is that somewhere in your range is an error of some kind. A word in a numeric column, or if you're using lookups in that range, you're getting a #N/A when something isn't found. To handle that, you could try to find and fix the data, or use the updated version below in column Q. You'll need to enter this version with Control+Shift+Enter.

	G	H	I	J	K	L	M	N	O	P	Q
1
2	Units				Sales	Channel	Category	Week		Rank
3	7				$18.00	Grocery	Apple	1		#N/A	2
4	7				$21.00	Grocery	Apple	1		#N/A	1
5	2				$6.00	Grocery	Apple	2		#N/A	1
6	5				$15.00	Grocery	Orange	2		#N/A	1
7	3				$9.00	Home	Apple	1		#N/A	2
8	9				$27.00	Home	Apple	1		#N/A	1
9	3				$9.00	Home	Apple	2		#N/A	1
10	5				$15.00	Restaurant	Orange	2		#N/A	2
11	7				$21.00	Restaurant	Orange	2		#N/A	1
12	1				$3.00	Restaurant	Orange	2		#N/A	3
13						#N/A				#N/A	1
14										#N/A	1

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

Sheet1​

Worksheet Formulas Cell Formula P3 =SUMPRODUCT(--($L$3:$L$15378=L3),--($M$3:$M$15378=M3),--($N$3:$N$15378=N3),--($G$3:$G$15378*100000+$K$3:$K$15378>G3*100000+K3))+1 L13 =NA() <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula Q3 {=SUMPRODUCT(--IFERROR(($L$3:$L$15378=L3),0),--IFERROR(($M$3:$M$15378=M3),0),--IFERROR(($N$3:$N$15378=N3),0),--IFERROR(($G$3:$G$15378*100000+$K$3:$K$15378>G3*100000+K3),0))+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>

 

[calecote]

Hey Eric!

YESSSS!!! You are a life saver!

Thanks,

Cale

 

[Eric W]

Glad to help!

 

[calecote]

Hi Eric,

I tried reaching out to the community for additional rank help and no one could answer my question so I was hoping you might be able to help.

I am having some issues with having rank total up the values. If I needed to rank the number one restaurant visited by people who have gone there and I have multiple line items for, and will not be able to combine them, how can I make rank choose the right one?

When I do a rank it says Chinese(75 ppl), but I want the formula to say Italian (90 ppl).

Restaurant	People there
Italian	20
Greek	17
Sushi	40
Chinese	75
Italian	35
Burger	17
Soup	30
Mexican	15
Italian	35

<tbody>
</tbody>

I don't want to create a separate sumifs table as most people are recommending because the restaurants will continue to grow and then I would have to make sure that the new restaurants are in the sumifs table as well.

Any Help would be great.

Thanks,

Cale

 

[Brian from Maui]

Have you tried a Pivot Table?