Monday, 11 December 2017

How to Calculate the Cumulative Sum or Running Total in Excel

Excel Formula to Calculate the Cumulative Sum or Running Total
The Cumulative Sum will be formed by or resulting from accumulation or the addition of successive parts or elements.
This Cumulative Sum is useful in analyzing the Business data, for example to identify the Sales Growth, by Calculating the Sum of Sales for MTD, QTD and YTD. 
Example:
Suppose we have the Sales data by Region, Product for 3 Quarter as follow..


Now We can calculate the Cumulative Sum with No Condition, with One Condition and with Multiple Conditions as follows..
Cumulative NetSales(with No Condition) :
Enter the below formula in Range ("F2") and drag till Range ("F13"). Now the Formula looks as follows, where the Range Begin is Locked at Column & Row level and the Range End is only Locked at Column level.
Range ("F2")=SUM($E$2:$E2)

Range ("F3")=SUM($E$2:$E3)

----
----
Range ("F13")=SUM($E$2:$E13)


Cumulative NetSales(with Condition on Region):
Enter the below formula in Range ("G2") and drag till Range ("G13"). Here we applied one Condition on Region which calculate the Cumulative Sum for each Region. The Cumulative Sum will break by each Region.
Range ("G2")=SUMIF($B$2:$B2,$B2,$E$2:$E2)

Range ("G3")=SUMIF($B$2:$B3,$B3,$E$2:$E3)

----
----
Range ("G13")=SUMIF($B$2:$B13,$B13,$E$2:$E13)

Cumulative NetSales(with Condition on Region and Product):
Enter the below formula in Range ("H2") and drag till Range ("H13"). Here we applied one Condition on Region and Product which calculate the Cumulative Sum for each Region by Product. The Cumulative Sum will break by each Product of that Region.
Range ("H2")=SUMIFS($E$2:$E2,$B$2:$B2,$B2,$C$2:$C2,$C2)

Range ("H3")=SUMIFS($E$2:$E3,$B$2:$B3,$B3,$C$2:$C3,$C3)

----
----
Range ("H13")=SUMIFS($E$2:$E13,$B$2:$B13,$B13,$C$2:$C13,$C13)

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts