Cumulative sum Value by rank and per group

[williamgiovanno]

Hi everyone,

I have to put an evaluation to the certain data set (ex : image below), and I'm attempting to create a summary for the cumulative sum of value by rank per group.

Column A is the number of data
Column B is the Group of each data
Column C is the value of each data
Column D is the rank (highest to smallest) of value of each data by their group

Column E is the cumulative sum that I tried to achieve, what i hoped for is the formula to calculate the cumulative sum of value but it is based on the rank per group (column D). In example, the E2 cell value is the cumulative sum of C8+C3+C6+C1= 152 (because at it's group (A) the data was ranked 4) , for the E3 cell value is the cumulative sum of C8+C3+C6= 127 (because at it's group (A) the data was ranked 3).

Hope that makes sense, appreciate it if anyone can help (or at least tell me that what I want isn't possible)

Thanks
William

 

[KRice]

Try this:

1650418844167.xlsx
	A	B	C	D	E
1	Data No.	Group	Value	Rank	Cumulative
2	1	A	25	4	152
3	2	A	43	2	94
4	3	B	250	1	250
5	4	B	212	4	941
6	5	A	33	3	127
7	6	C	100	4	440
8	7	A	51	1	51
9	8	B	235	3	729
10	9	B	244	2	494
11	10	C	114	2	235
12	11	C	121	1	121
13	12	C	105	3	340
Sheet1

Cell Formulas
Range	Formula
E2:E13	E2	=SUMIFS($C$2:$C$13,$D$2:$D$13,"<="&D2,$B$2:$B$13,B2)

 

[williamgiovanno]

T

Try this:

1650418844167.xlsx
	A	B	C	D	E
1	Data No.	Group	Value	Rank	Cumulative
2	1	A	25	4	152
3	2	A	43	2	94
4	3	B	250	1	250
5	4	B	212	4	941
6	5	A	33	3	127
7	6	C	100	4	440
8	7	A	51	1	51
9	8	B	235	3	729
10	9	B	244	2	494
11	10	C	114	2	235
12	11	C	121	1	121
13	12	C	105	3	340
Sheet1

Cell Formulas
Range	Formula
E2:E13	E2	=SUMIFS($C$2:$C$13,$D$2:$D$13,"<="&D2,$B$2:$B$13,B2)

Thank you very much, this is what i have been looking for.

 

[KRice]

Glad to help...and welcome to the MrExcel Forum.