How to Calculate Running Total in One Cell in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

A lot of times we need to find out the Running Total in our Excel datasheet instead of the Grand Total. It’s also desired that the running total gets updated every time we input new values. Again, computing the running total in a single cell is a challenge as we often calculate the running total cell by cell. In this article, we will show you the simple yet effective ways to Calculate the Running Total in One Cell in Excel.


Dataset Introduction

To illustrate, we are going to use a sample dataset as an example. For instance, the following dataset represents the Month, Product, and Sales of a company. Here, we’ll calculate the sum of the Monthly Sales.

excel running total in one cell


Introduction to Running Total in Excel

The partial summation of a sequence of numbers which gets updated every time a new number is added to the sequence is known as Running Total in Excel. It is also called Cumulative Sum. A Running Total displays a summary of data as it grows over time. We use the Running Total in Excel when it especially involves the daily data such as daily sales, daily bank balance, etc.


Exel Running Total in One Cell in Excel: 5 Ways to Calculate

1. Calculate Running Total in One Cell in Excel with SUM Function

The SUM function helps us to compute the total amount of a range of cells very easily. In our first method, we’ll use this function to find out the Running Total. Therefore, follow the steps below to Calculate the Running Total in One Cell in Excel.

STEPS:

  • First, select cell D12 and type the formula:
=SUM(D5:D10)
  • Then, press Enter.

Calculate Running Total in One Cell in Excel with SUM Function

  • Now, input the Sales for the month of May.
  • As a result, you’ll see that the total in cell D12 will get updated automatically.


2. Use the Addition Operator to Evaluate Running Total in One Cell

However, you can also create a simple formula by using the Addition Operator to compute the Running Total. So, learn the process to perform the task.

STEPS:

  • Firstly, select cell D12.
  • subsequently, type the formula:
=D5+D6+D7+D8+D9+D10
  • After that, press Enter.

Use the Addition Operator to Evaluate Running Total in One Cell

  • Next, input the new Sales data.
  • Eventually, it’ll return the revised Running Total.

Read More: How to Calculate Horizontal Running Total in Excel


3. Insert Excel Table for Calculating Running Total in One Cell

Additionally, we can use the Header in the SUM function instead of referencing the cell range. But for that, we have to insert a Table first. Hence, follow the below process to carry out the operation.

STEPS:

  • First of all, select the range B4:D10.
  • Afterward, press the keys ‘Ctrl’ and ‘+’ simultaneously.
  • Consequently, the Create Table dialog box will pop out and press OK.

Insert Excel Table for Calculating Running Total in One Cell

  • As a result, you’ll get a table named Table1.

Insert Excel Table for Calculating Running Total in One Cell

  • Now, in cell D12, type the formula:
=SUM(Table1[Sales])
  • Then, press Enter.

  • After that, type the new Sales data for May and June.
  • Finally, the Running Total will get updated by itself in cell D12.


4. Compute Running Total Based on Criteria

Sometimes, we have to evaluate the Running Total based on criteria. In the following dataset, we have 2 Products: Cable and AC. Here, we’ll figure out the Running Total of the Sales for the product Cable. For that purpose, we’ll use the SUMIF function. Therefore, learn the below steps to Calculate the Running Total in One Cell in Excel.

Compute Running Total Based on Criteria

STEPS:

  • Firstly, select cell D12.
  • Type the formula:
=SUMIF(C5:C10,C5,D5:D10)
  • Then, press Enter.

Compute Running Total Based on Criteria

  • After that, insert the new Sales data for the month of March.
  • Consequently, you’ll see the refreshed Running Total.

Read More: Cumulative Sum in Excel If Condition Applied


5. Excel VBA to Calculate Running Total in One Cell

In our last method, we’ll apply Excel VBA for calculating the Running Total. It’s a little different from the previous methods. Here, we’ll input the new value that we need to add to the already existing data. Hence, follow the steps to know how to perform the task.

STEPS:

  • In the beginning, we have a value in cell A1.
  • Now, select the sheet and right-click on the mouse.
  • Then, select View Code.

Excel VBA to Calculate Running Total in One Cell

  • As a result, the VBA window will emerge and a dialog box will appear.
  • Copy the following code and paste it into the box.
Dim RangeValue As Double
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo EndF
Application.EnableEvents = False
If Target.Count = 1 Then
If (Len(Target.Range("A1").Value) > 0) And IsNumeric(Target.Range("A1").Value) Then
If Target.Range("A1").Value = 0 Then RangeValue = 0
Target.Range("A1").Value = 1 * Target.Range("A1").Value + RangeValue
End If
End If
EndF:
  Application.EnableEvents = True
RangeValue = 0
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo err0
If Target.Count = 1 Then
If (Len(Target.Range("A1").Value) > 0) And IsNumeric(Target.Range("A1").Value) Then
RangeValue = Target.Range("A1").Value
End If
End If
err0:
End Sub

Excel VBA to Calculate Running Total in One Cell

  • Next, close the VBA window.
  • Now, type the new value that you want to add in cell A1.
  • In this example, type 1000.

Excel VBA to Calculate Running Total in One Cell

  • Subsequently, press Enter and it’ll return the computed result.

Excel VBA to Calculate Running Total in One Cell

  • Again, type 2000.

  • Consequently, press Enter and you’ll see the updated total amount.


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to Calculate the Running Total in One Cell in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Excel Running Total | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo