Normal and Array Based Running Total Formula in Google Sheets

0
67
Array Based Running Total Formula in Google Sheets

I’ve extensively used cumulative sum or running total to plot S Curves as part of Progress Reports related to my job. You can get a running total in Google Sheets in different ways. Here I am providing you three different formulas to do running total in Google Sheets. There are normal as well as array based running total formula in Google Sheets.

In real life, there are several instances where we can use running totals. A cricket match score is an example to Running total. In cricket the total score is the summation of the Runs. Each time a player scores runs it added to the sequence. Below column C is an example to Running Total in Cricket.

running total example in google doc spreadsheet

If you are not familiar with Cricket, you can ignore every thing except the numbers in the above sample data. You just understand that the sum of B2:B6 is equal to value in C6. Here column C contains the running total of Column B.

Formula # 1 : Normal Using Cumulative Sum or Running Total Formula

Here is the basic or the simplest form of running total calculation in Google Sheets.

Normal Cumulative Sum or Running Total Formula

Here in Cell C2 use the formula “=B2”. Now in C3 use the formula shown in the image and copy and paste it to down. Your cumulative sum is ready.

Formula # 2 : Another Variant of Normal Running Total Formula

Here is another similar approach to get cumulative sum in Google Sheets. This is a tricky use of SUM function. You can use the below formula directly in Cell C2 and then copy and paste to cells below in the column.

=sum($B$2:B2)

Here the use of Dollar symbol makes the cell B2 obsolete. After copying this to downwards check the formula in Cell C3 for reference. It would be like;

=sum($B$2:B3)

Hope it makes sense. Now let us see how to do a running total in Google Sheets as an array. This’s little complicated compare to the above two.

Formula # 3 : Array Based Running Total Formula in Google Sheets

A one line array formula can return the running total in Google Sheets. You just need to key in the below formula in Cell C2.

=ArrayFormula(If(len(B2:B),(SUMIF(ROW(B2:B),“<=”&ROW(B2:B),B2:B)),))

Formula Explanation:

The core part of the above running total formula is the SUMIF function. Compare the below syntax with the above formula with the help of colour pattern. So you can easily understand the arguments in the formula.

SUMIF(range, criterion, [sum_range])

Let’s see the role of ROW function which acts as “range” here. When you individually use the ROW function as below, it returns the row numbers of the cells in the range.

=ArrayFormula(If(len(B2:B),ROW(B2:B10),))

To see the result of this function, see the Column E in the below screenshot.

As you told the output of the ROW formula is the “range” in our SUMIF formula. The SUMIF formula checks this range with criterion. Here criterion is also same numbers returned by the ROW formula.

Note: Refer my Google Sheets Function Guide if you have any doubt in using any of the formulas mentioned here.

To make it more clear I’ve individually applied the row formula in Cell E2 and F2, the SUM range in Column G2:G6 which is from our sample data B2:B6. See that below. I’ve modified our original Running Total Array formula here to accommodate the new values returned by ROW function. You can see the modified SUMIF formula in H2 that returns the running total as an array.

Note: It’s for explanation purpose only. You should stick with my original formula given above that contains the ROW function.

sumif in running total in google sheets

This formula Sums Column G where ever the conditions meet. For example in E4 the value is 4. The formula checks whether this value is <= the value in F4. It matches the condition so sums column G up to that range. That’s all. It’s applicable to other cells in the range.

If you have still doubt, here is my Google Sheet containing the above Array Based Running Total Formula in Google Sheets. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here