Rankif with 2 criteria

Hi guys,

I need to rank using 2 criteria (the people and the group of products) based on the cost. I attached the excel file for easy understanding.

Thanks!

Updated file - 4/9/14

Originally Posted by huy_le

Hi Azumi,

That's part of the worksheet because it's part of the lookup and some do not have data. That is why I want to ignore the errors if it is possible with formula.

Include error trapping in your lookup to remove the error

Can you update your sample file to show us what results you expect?

Data Range

	A	B	C	D	E
1	People	Product	Cost		What I want
2	A	Glass	500		2
3	A	Bottle	700		3
4	A	Glass	200		2
5	A	Cup	900		1
6	A				None
7	B	Cup	100		2
8	B	Glass	400		3
9	B	Cup	500		2
10	B	Bottle	700		1
11	B				None
12	C	Bottle	800		1
13	C	Cup	300		2
14	C	Glass	200		3
15	C	Bottle	300		1
16	C				None
17	------	------	------	------	------

Can you explain the logic needed to arrive at those results?

Do you mean like this...

A - Cup - total = 900 = 1
A - Glass - total = 700 = 2
A - Bottle - total = 700 = 3

Are you able to change the data structure to something like this:

Data Range

	G	H	I	J
1	People	Product	Cost	Rank
2	A	Glass	700	2
3	A	Bottle	700	3
4	A	Cup	900	1
5	B	Glass	400	3
6	B	Bottle	700	1
7	B	Cup	600	2
8	C	Glass	200	3
9	C	Bottle	1100	1
10	C	Cup	300	2

Well, nothing I've tried works.

You've stumped me!

Hi.

Please click on Attachment. Information are in there.

regard
micope21

Almost...but stumped on tie totals,

=IF(B3="","None",MATCH(SUMIFS(C$2:C$16,B$2:B$16,B3,A$2:A$16,A3),LARGE(SUMIFS(C$2:C$16,A$2:A$16,A3,B$2:B$16,LOOKUP(IF(MATCH(A3&"_"&B$2:B$16,A$2:A$16&"_"&B$2:B$16,0)=ROW(A$2:A$16)-ROW(A$2)+1,ROW(A$2:A$16)),ROW(A$2:A$16),B$2:B$16)),ROW(A$2:A$16)-ROW(A$2)+1),0))

Or,

=IF(B2="","None",SUM(IF(SUMIFS(C$2:C$16,B$2:B$16,B2,A$2:A$16,A2)<SUMIFS(C$2:C$16,A$2:A$16,A2,B$2:B$16,LOOKUP(IF(MATCH(A2&"_"&B$2:B$16,A$2:A$16&"_"&B$2:B$16,0)=ROW(A$2:A$16)-ROW(A$2)+1,ROW(A$2:A$16)),ROW(A$2:A$16),B$2:B$16)),1))+1)

No doubt it's complicated but I'm sure someone can do this.

The right person just hasn't seen this thread. Let me put a link to it in the "Call in the Cavalry" thread.

Can someone explain the logic to me? Baffled.

Regards

Data Range

	A	B	C	D	E
1	People	Product	Cost		What I want
2	A	Glass	500		2
3	A	Bottle	700		3
4	A	Glass	200		2
5	A	Cup	900		1
6	A				None
7	B	Cup	100		2
8	B	Glass	400		3
9	B	Cup	500		2
10	B	Bottle	700		1
11	B				None
12	C	Bottle	800		1
13	C	Cup	300		2
14	C	Glass	200		3
15	C	Bottle	300		1
16	C				None
17	------	------	------	------	------

E2 = the rank of SUMIFS(C:C,A:A,A2,B:B,B2) if column A = A2
E3 = the rank of SUMIFS(C:C,A:A,A3,B:B,B3) if column A = A3
E4 = the rank of SUMIFS(C:C,A:A,A4,B:B,B4) if column A = A4
etc
etc
etc

Thanks, Tony, that's great.

Not sure I understand why E2, E3 and E4 aren't all 2, but perhaps this in E2 and copied down:

=IF(B2="","None",1+SUMPRODUCT(0+(SUMIFS(C$2:C$200,A$2:A$200,A2,B$2:B$200,B$2:B$200)>SUMIFS(C$2:C$200,A$2:A$200,A2,B$2:B$200,B2)))/COUNTIFS(B$2:B$200,B2))

Amend the 200 to a suitably higher value if required.

Regards

@ XOR...

For group A I think the desired results are: 2, 3, 2, 1.

Your formula returns: 2, 2, 2, 1.

It looks like they want a tie breaker similar to:

Data Range

	A	B	C	D
1	Group	Item	Value	Rank
2	A	X	10	1
3	A	X	10	1
4	A	Y	10	2
5	A	Z	8	3
6	A	Z	8	3

Originally Posted by XOR LX

=IF(B2="","None",1+SUMPRODUCT(0+(SUMIFS(C$2:C$200,A$2:A$200,A2,B$2:B$200,B$2:B$200)>SUMIFS(C$2:C$200,A$2:A$200,A2,B$2:B$200,B2)))/COUNTIFS(B$2:B$200,B2))

Doesn't this work here simply because Cup, Bottle and Glass all occur an equal number of times? What results do you get if you change B2 to Cup?

Originally Posted by daddylonglegs

Doesn't this work here simply because Cup, Bottle and Glass all occur an equal number of times? What results do you get if you change B2 to Cup?

Good point.

Apologies to all interested parties, especially the OP, for the lack of rigour.

Back to the drawing board. May be a simple fix, though I'm not initially hopeful.

Regards

Hi. Maybe this formula can help? Paste to E2 cel and then Ctrl+Shift+Enter

=IF((SUM(--(SUMIF(OFFSET($A$1,MATCH(A2,A:A,0)-1,1,COUNTIF(A:A,A2)-1,1),B2,OFFSET($A$1,MATCH(A2,A:A,0)-1,2,COUNTIF(A:A,A2)-1,1))<OFFSET($A$1,MATCH(A2,A:A,0)-1,2,COUNTIF(A:A,A2)-1,1)))+1)*ISNUMBER(C2)>SUM(1/COUNTIF(OFFSET($A$1,MATCH(A2,A:A,0)-1,1,COUNTIF(A:A,A2)-1,1),OFFSET($A$1,MATCH(A2,A:A,0)-1,1,COUNTIF(A:A,A2)-1,1))),3,(SUM(--(SUMIF(OFFSET($A$1,MATCH(A2,A:A,0)-1,1,COUNTIF(A:A,A2)-1,1),B2,OFFSET($A$1,MATCH(A2,A:A,0)-1,2,COUNTIF(A:A,A2)-1,1))<OFFSET($A$1,MATCH(A2,A:A,0)-1,2,COUNTIF(A:A,A2)-1,1)))+1)*ISNUMBER(C2))

Only for Group A - Bottle rank will give you incorrect answer. I think with helper columns solution is possible.

I used helper table.
=IFERROR(INDEX(RANK(INDEX($I$2:$L$10,0,MATCH(A2,$I$1:$L$1,0)),INDEX($I$2:$L$10,0,MATCH(A2,$I$1:$L$1,0))),MATCH(B2,$H$2:$H$10,0)),"")

For more information see the attached file.

In previous formula (of mine) E2, e3 e4 was giving same answer because their results were same (700).

Can someone check this:
(one helper column)

I think the goal here is to not have to use helper columns.

So did my solution not give you that?

Regards

You're welcome!

Given that ties should be ranked the same Haseeb's suggestion works for me

Originally Posted by Haseeb A

=IF(B2="","None",SUM(IF(SUMIFS(C$2:C$16,B$2:B$16,B2,A$2:A$16,A2)<SUMIFS(C$2:C$16,A$2:A$16,A2,B$2:B$16,LOOKUP(IF(MATCH(A2&"_"&B$2:B$16,A$2:A$16&"_"&B$2:B$16,0)=ROW(A$2:A$16)-ROW(A$2)+1,ROW(A$2:A$16)),ROW(A$2:A$16),B$2:B$16)),1))+1)

.......although I think you can eliminate the LOOKUP and use this slightly shorter version

Formula:

=IF(B2="","None",SUM(IF(SUMIFS(C$2:C$16,B$2:B$16,B2,A$2:A$16,A2)<SUMIFS(C$2:C$16,A$2:A$16,A2,B$2:B$16,IF(MATCH(A2&"_"&B$2:B$16,A$2:A$16&"_"&B$2:B$16,0)=ROW(A$2:A$16)-ROW(A$2)+1,B$2:B$16)),1))+1)

Either way, needs to be confirmed with CTRL+SHIFT+ENTER