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.

Client :Project #	Revenue	Rank Me
Project 1	$345.00
Project 1	$1,769.00
Project 1	$363.00
Project 2	$314.00
Project 2	$284.00
Project 2	$190.00
Project 2	$2,350.00
Project 2	$164.00
Project 4	$90.00
Project 4	$38.00
Project 4	$38.00
Project 4	$20.00
Project 4	$14.00
Project 3	$113.00
Project 5	$1,398.00

<tbody>
</tbody>

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.

Client :Project #	Revenue	Rank Me
Project 1	$345.00	2
Project 1	$1,769.00	2
Project 1	$363.00	2
Project 2	$314.00	1
Project 2	$284.00	1
Project 2	$190.00	1
Project 2	$2,350.00	1
Project 2	$164.00	1
Project 4	$90.00	4
Project 4	$38.00	4
Project 4	$38.00	4
Project 4	$20.00	4
Project 4	$14.00	4
Project 3	$113.00	5
Project 5	$1,398.00	3

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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
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	*	*	*	*

<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>
</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.