Cumulative sum Value by rank and per group

williamgiovanno

New Member
Joined
Apr 19, 2022
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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).
1650418844167.png


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

Thanks :)
William
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,235
Office Version
  1. 365
Platform
  1. Windows
Try this:
1650418844167.xlsx
ABCDE
1Data No.GroupValueRankCumulative
21A254152
32A43294
43B2501250
54B2124941
65A333127
76C1004440
87A51151
98B2353729
109B2442494
1110C1142235
1211C1211121
1312C1053340
Sheet1
Cell Formulas
RangeFormula
E2:E13E2=SUMIFS($C$2:$C$13,$D$2:$D$13,"<="&D2,$B$2:$B$13,B2)
 
Solution

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,235
Office Version
  1. 365
Platform
  1. Windows
Glad to help...and welcome to the MrExcel Forum.
 

Forum statistics

Threads
1,181,212
Messages
5,928,724
Members
436,625
Latest member
agholson81

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top