# Formula to calculate cumulative percentage in multiple data sets

#### explosive_duck

##### New Member
Hi,

I am trying to find a quick way to calculate the cumulative value and percentage for multiple sets of data and below is an example of the data I have and the last 2 columns is what I am trying to calculate.

StoreCategoryArticleSales UnitsCum SalesCum %
ANecklaces5424170575724%
A
 Necklaces

<tbody>
</tbody>
 5324323

<tbody>
</tbody>
5411149%
A
 Necklaces

<tbody>
</tbody>
 5324324

<tbody>
</tbody>
4315468%
A
 Necklaces

<tbody>
</tbody>
 5515300

<tbody>
</tbody>
3819285%
A
 Necklaces

<tbody>
</tbody>
 5967371

<tbody>
</tbody>
33225100%
AEarrings
 1952456

<tbody>
</tbody>
515137%
A
 Earrings

<tbody>
</tbody>
 5722546

<tbody>
</tbody>

<tbody>
</tbody>
5110274%
A
 Earrings

<tbody>
</tbody>
 5967283

<tbody>
</tbody>
36138100%
AWatches
 5515400

<tbody>
</tbody>
141429%
AWatches
 5874077

<tbody>
</tbody>
102450%
AWatches
 5967500

<tbody>
</tbody>
93369%
AWatches
 5874078

<tbody>
</tbody>
84185%
AWatches
 5874081

<tbody>
</tbody>
748100%
BNecklaces5324323121280%
BNecklaces5424170315100%
BWatches5515400101048%
BWatches587407791990%
BWatches587407812095%
BWatches5874081121100%

<tbody>
</tbody>

Cum Sales

This is basically where I need a running total by store and category in a descending order

Cum %

This is where I need the percentage of the cumulative sales by store and category.

Any help with this would be greatly appreciated.

Thanks.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try:

E2: =SUMIFS(\$D\$2:\$D2,\$A\$2:\$A2,A2,\$B\$2:\$B2,B2)
F2: =E2/SUMIFS(\$E\$2:\$E\$20,\$A\$2:\$A\$20,A2,\$B\$2:\$B\$20,B2)

Drag down the column.

Thanks Eric. The first formula works but the second for the percentage doesn't.

For the percentage, I need it to divide the individual cumulative sales in column E with the total sales for the category.

As an example:

For Necklaces, on the first article, I need it to do 57/225

Sorry, the F2 formula should be:

=E2/SUMIFS(\$D\$2:\$D\$20,\$A\$2:\$A\$20,A2,\$B\$2:\$B\$20,B2)

Last edited:

Replies
34
Views
547
Replies
1
Views
105
Replies
48
Views
716
Replies
1
Views
85
Replies
3
Views
164

1,196,481
Messages
6,015,451
Members
441,896
Latest member
clomah

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

### Which adblocker are you using?

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

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