Formula to calculate cumulative percentage in multiple data sets

[explosive_duck]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Category[/TD]
[TD]Article[/TD]
[TD]Sales Units[/TD]
[TD]Cum Sales[/TD]
[TD]Cum %[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Necklaces[/TD]
[TD]5424170[/TD]
[TD]57[/TD]
[TD]57[/TD]
[TD]24%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl66, width: 83"]Necklaces[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5324323[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]54[/TD]
[TD]111[/TD]
[TD]49%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl66, width: 83"]Necklaces[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5324324[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]43[/TD]
[TD]154[/TD]
[TD]68%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl66, width: 83"]Necklaces[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5515300[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]38[/TD]
[TD]192[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl66, width: 83"]Necklaces[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5967371[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]33[/TD]
[TD]225[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Earrings[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]1952456[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]37%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl66, width: 83"]Earrings[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5722546[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]51[/TD]
[TD]102[/TD]
[TD]74%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl66, width: 83"]Earrings[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5967283[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]36[/TD]
[TD]138[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Watches[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5515400[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]14[/TD]
[TD]14[/TD]
[TD]29%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Watches[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5874077[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[TD]24[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Watches[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5967500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9[/TD]
[TD]33[/TD]
[TD]69%
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Watches[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5874078[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8[/TD]
[TD]41[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Watches[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]5874081[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]7[/TD]
[TD]48[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Necklaces[/TD]
[TD]5324323[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Necklaces[/TD]
[TD]5424170[/TD]
[TD]3[/TD]
[TD]15[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Watches[/TD]
[TD]5515400[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]48%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Watches[/TD]
[TD]5874077[/TD]
[TD]9[/TD]
[TD]19[/TD]
[TD]90%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Watches[/TD]
[TD]5874078[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]95%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Watches[/TD]
[TD]5874081[/TD]
[TD]1[/TD]
[TD]21[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Eric W]

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.

 

[explosive_duck]

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

 

[Eric W]

Sorry, the F2 formula should be:

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