Calculate Cumulative Sum in Same Cell in Excel

Question

I want to calculate total stock quantity of each item separately. I have last column name "Total Stock Quantity" in which I want sum separately.

Condition: Whenever I give new quantity to the item, it should get added/subtract to same cell "Total Stock Quantity".

I have attached screen shot for example.

Here initially "Total Stock Quantity" is 0 and I am adding an item (quantity=15) to list:

Now "Total Stock Quantity" should be 15

Now I am giving new quantity for that item (quantity=10)

Now the "Total Stock Quantity" should be 25 which is Previous Value (15) + New Value (10)

Basically, it should get updated every time I enter new stock unit for my item

So, what should I do to make Cumulative sum for the same cell.

Please Provide me a solution for this :)

Please provide what you have tried. Then we can help you to fix or improve it. This is no free code writing service. Also you need to ask a proper question (How to Ask). If you have done nothing yet check out how to use the Worksheet_Change event and come back with an attempt. — Pᴇʜ
Should this be for only a single cell or for a column of cells ??? — Gary's Student
Use a worksheet_change event macro to determine when you should add to the Total cell. Be aware that this method will eliminate the ability to track your changes; troubleshoot data entry errors, etc. A better method might be to have a worksheet for a history of transactions, and then display this (your table as shown in your question) on a summary sheet. — Ron Rosenfeld

Danilo Danilo · Accepted Answer · 2020-06-08T13:26:17

The code below is an example that does what you described as you need. For your workbook/project, please consider the warnings in Ron Rosenfeld's comment.

Changes in the cell "E2" is the trigger to a macro that do the sum and update the other cell value. In this example, it'll sum the value added in the cell "E2" to the value in cell "G2". It'll have to be updated given your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$2" Then
        Call Update_TsQ
    End If
End Sub

Sub Update_TsQ()

Dim QiS As Long
Dim TsQ As Long

QiS = Range("E2").Value
TsQ = Range("G2").Value

TsQ = TsQ + QiS
Range("G2").Value = TsQ

End Sub

Calculate Cumulative Sum in Same Cell in Excel

1 Answers