Rank by sumifs (To Get unique rank count to each line)

[Pinkal]

Hello Experts,

CASE: I have Five projects but having different revenue generation for each, So I need to find out the best project by Cumulative Revenue and give them the rank (1 to 5).

Requirement: Want to get Ranking from 1 to 5 against each project based on their cumulative Revenue without help of Pivot and extra columns.


[TABLE="width: 251"]
<tbody>[TR]
[TD]Client :Project #[/TD]
[TD]Revenue[/TD]
[TD]Rank Me[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD="align: right"]$345.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD="align: right"]$1,769.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD="align: right"]$363.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$314.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$284.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$190.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$2,350.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$164.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$90.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$38.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$38.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$20.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$14.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD="align: right"]$113.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 5[/TD]
[TD="align: right"]$1,398.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have been searching for solution in various sites, but couldn't find any scenario which address the three criteria: sum of Revenue + repeat names + Ranking..

Please help.


Outcome should be like this :

However i have done this by creating Pivot and then vloomup but idea is that how can i do it without having an extra column or pivot.


[TABLE="width: 251"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Client :Project #[/TD]
[TD]Revenue[/TD]
[TD]Rank Me[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD="align: right"]$345.00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD="align: right"]$1,769.00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD="align: right"]$363.00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$314.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$284.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$190.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$2,350.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD="align: right"]$164.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$90.00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$38.00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$38.00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD="align: right"]$14.00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD="align: right"]$113.00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Project 5[/TD]
[TD="align: right"]$1,398.00[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Copy E2:F2 down.

Excel Workbook
	A	B	C	D	E	F
1	Client :Project #	Revenue		Project	Cum$	Rank
2	Project 1	$345.00		Project 1	$2,477.00	2
3	Project 1	$1,769.00		Project 2	$3,302.00	1
4	Project 1	$363.00		Project 3	$113.00	5
5	Project 2	$314.00		Project 4	$200.00	4
6	Project 2	$284.00		Project 5	$1,398.00	3
7	Project 2	$190.00
8	Project 2	$2,350.00
9	Project 2	$164.00
10	Project 4	$90.00
11	Project 4	$38.00
12	Project 4	$38.00
13	Project 4	$20.00
14	Project 4	$14.00
15	Project 3	$113.00
16	Project 5	$1,398.00
Sheet1

 

[Pinkal]

Copy E2:F2 down.
Sheet1

*	A	B	C	D	E	F
Client :Project #	Revenue	*	Project	Cum$	Rank
Project 1	*	Project 1
Project 1	*	Project 2
Project 1	*	Project 3
Project 2	*	Project 4
Project 2	*	Project 5
Project 2	*	*	*	*
Project 2	*	*	*	*
Project 2	*	*	*	*
Project 4	*	*	*	*
Project 4	*	*	*	*
Project 4	*	*	*	*
Project 4	*	*	*	*
Project 4	*	*	*	*
Project 3	*	*	*	*
Project 5	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:106px;"><col style="width:69px;"><col style="width:23px;"><col style="width:61px;"><col style="width:69px;"><col style="width:37px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

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

[TD="align: right"]$2,477.00 [/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]$1,769.00 [/TD]

[TD="align: right"]$3,302.00 [/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

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

[TD="align: right"]$113.00 [/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

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

[TD="align: right"]$200.00 [/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

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

[TD="align: right"]$1,398.00 [/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

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

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]$2,350.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

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

[TD="bgcolor: #cacaca, align: center"]10[/TD]

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

[TD="bgcolor: #cacaca, align: center"]11[/TD]

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

[TD="bgcolor: #cacaca, align: center"]12[/TD]

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

[TD="bgcolor: #cacaca, align: center"]13[/TD]

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

[TD="bgcolor: #cacaca, align: center"]14[/TD]

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

[TD="bgcolor: #cacaca, align: center"]15[/TD]

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

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]$1,398.00 [/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 =SUMIF($A$2:$A$16,D2,$B$2:$B$16) F2 =RANK(E2,$E$2:$E$6) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hello JoeMo,

Thank your reply, however I need only one formula which can give me the rank.. what you have shared is supporting column D and E, Can we get formula where we don't need to have supporting column.

Thank you.

 

[JoeMo]

Hello JoeMo,

Thank your reply, however I need only one formula which can give me the rank.. what you have shared is supporting column D and E, Can we get formula where we don't need to have supporting column.

Thank you.

You could hide the supporting columns with this.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Client :Project #	Revenue	Rank			Project	Cum$	Rank
2	Project 1	$345.00	2			Project 1	$2,477.00	2
3	Project 1	$1,769.00	2			Project 2	$3,302.00	1
4	Project 1	$363.00	2			Project 3	$113.00	5
5	Project 2	$314.00	1			Project 4	$200.00	4
6	Project 2	$284.00	1			Project 5	$1,398.00	3
7	Project 2	$190.00	1
8	Project 2	$2,350.00	1
9	Project 2	$164.00	1
10	Project 4	$90.00	4
11	Project 4	$38.00	4
12	Project 4	$38.00	4
13	Project 4	$20.00	4
14	Project 4	$14.00	4
15	Project 3	$113.00	5
16	Project 5	$1,398.00	3
Sheet1

 

[barry houdini]

If you want a single formula for ranking try this formula in C2 copied down

=SUMPRODUCT((SUMIF(A$2:A$16,A$2:A$16,B$2:B$16)*(MATCH(A$2:A$16,A$2:A$16,0)=ROW(A$2:A$16)-ROW(A$2)+1)>SUMIF(A$2:A$16,A2,B$2:B$16))+0)+1

 

[Pinkal]

Hello Barry,
your one cell formula addressed my requirement and its working as i wanted. Thank you so much.

@JoeMo, Thank you so much for your replies.