Aung Shine
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.
Download Practice Workbook
Download the following workbook to practice by yourself.
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.
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.
5 Ways to Calculate Running Total in One Cell in Excel
1. Calculate Running Total in One Cell in Excel with SUM Function
The Excel 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.
- 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.
Read More: Quick Analysis Tool: Calculation of Running Total in Excel (4 Ways)
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.
- 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 Ways)
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.
- As a result, you’ll get a table named Table1.
- 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.
Read More: How to Use Pivot Table to Calculate Running Total by Date in Excel
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.
STEPS:
- Firstly, select cell D12.
- Type the formula:
=SUMIF(C5:C10,C5,D5:D10)- Then, press Enter.
- 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 (6 Methods)
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.
- 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
- Next, close the VBA window.
- Now, type the new value that you want to add in cell A1.
- In this example, type 1000.
- Subsequently, press Enter and it’ll return the computed result.
- Again, type 2000.
- Consequently, press Enter and you’ll see the updated total amount.
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.
