Rank by Sumifs

[ryan640]

Hello Everyone,

I am trying to rank a row by the total volume but have a few criteria. For the below example I would need to rank what project has the highest revenue however I cant just look at project. I also have to look at product and in scope vs out of scope.

I found a link to someone doing this with a sumif function but was unable to make it work with multiple criteria. Can someone help me adjust this formula to make it work for if I had 3 criteria?

=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

In Scope	Client :Project #	Product	Revenue	Rank Me
y	Project 1	Product A	$345.00
y	Project 1	Product A	$1,769.00
n	Project 1	Product A	$363.00
y	Project 2	Product A	$314.00
y	Project 2	Product B	$284.00
n	Project 2	Product B	$190.00
y	Project 2	Product B	$2,350.00
y	Project 2	Product B	$164.00
y	Project 4	C	$90.00
y	Project 4	C	$38.00
y	Project 4	C	$38.00
y	Project 4	C	$20.00
y	Project 4	D	$14.00
y	Project 3	D	$113.00
y	Project 5	A	$1,398.00

Link to the other post:

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

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

www.mrexcel.com

 

[DanteAmor]

How about:

varios 24mar2022.xlsm
	A	B	C	D	E
1	In Scope	Client :Project #	Product	Revenue	Rank Me
2	y	Project 1	Product A	$345.00	2
3	y	Project 1	Product A	$1,769.00	2
4	n	Project 1	Product A	$363.00	4
5	y	Project 2	Product A	$314.00	5
6	y	Project 2	Product B	$284.00	1
7	n	Project 2	Product B	$190.00	6
8	y	Project 2	Product B	$2,350.00	1
9	y	Project 2	Product B	$164.00	1
10	y	Project 4	C	$90.00	7
11	y	Project 4	C	$38.00	7
12	y	Project 4	C	$38.00	7
13	y	Project 4	C	$20.00	7
14	y	Project 4	D	$14.00	9
15	y	Project 3	D	$113.00	8
16	y	Project 5	A	$1,398.00	3
Hoja7

Cell Formulas
Range	Formula
E2:E16	E2	=SUMPRODUCT((SUMIFS(D$2:D$16,A$2:A$16,A$2:A$16,B$2:B$16,B$2:B$16,C$2:C$16,C$2:C$16)*(MATCH(A$2:A$16&"|"&B$2:B$16&"|"&C$2:C$16,A$2:A$16&"|"&B$2:B$16&"|"&C$2:C$16,0)=ROW(C$2:C$16)-ROW(C$2)+1)>SUMIFS(D$2:D$16,A$2:A$16,A2,B$2:B$16,B2,C$2:C$16,C2))+0)+1

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have a recent version with dynamic array functions and the LET function, and assuming the results shown in post #2 are what you want, you can do this with a single formula in a single cell (no need to copy down).

22 03 25.xlsm
	A	B	C	D	E
1	In Scope	Client :Project #	Product	Revenue	Rank Me
2	y	Project 1	Product A	$345.00	2
3	y	Project 1	Product A	$1,769.00	2
4	n	Project 1	Product A	$363.00	4
5	y	Project 2	Product A	$314.00	5
6	y	Project 2	Product B	$284.00	1
7	n	Project 2	Product B	$190.00	6
8	y	Project 2	Product B	$2,350.00	1
9	y	Project 2	Product B	$164.00	1
10	y	Project 4	C	$90.00	7
11	y	Project 4	C	$38.00	7
12	y	Project 4	C	$38.00	7
13	y	Project 4	C	$20.00	7
14	y	Project 4	D	$14.00	9
15	y	Project 3	D	$113.00	8
16	y	Project 5	A	$1,398.00	3
Rank Sumifs

Cell Formulas
Range	Formula
E2:E16	E2	=LET(ca,A2:A16,cb,B2:B16,cc,C2:C16,z,"|",j,ca&z&cb&z&cc,u,UNIQUE(j),p,FIND(z,u),q,FIND(z,u,p+1), si,SUMIFS(D2:D16,ca,LEFT(u,p-1),cb,MID(u,p+1,q-p-1),cc,REPLACE(u,1,q,"")),MATCH(j,SORTBY(u,si,-1),0))
Dynamic array formulas.