CODEWITHSUNDEEP
excelexcel-formula
jacksons123
jacksons123

Reputation: 181

Have A Dynamic Sum Function in Excel

So, I feel like I'm trying to do something fairly simple in Excel here. My company has a Spreadsheet with 2 columns and an indefinite number of rows. Column A is the Date, Column B is a production number for that date. At the bottom of Column B is the total production number. However, every time we update the spreadsheet we have to update the formula to include the newest data on every page. We insert columns for the new dates, so is it possible to have a function that changes based on it's current coordinate? For example something like =SUM(B3:B(CurrentRow-1)).

Upvotes: 0

Views: 84

Answers (2)

Eby Jacob
Eby Jacob

Reputation: 1458

Hi You can use the below excel formula to achieve the result you are looking at. If the values you want to sum up is in column B, then type the below formula and it will show the value dynamically when ever you have added a value to the column.

=SUM(INDIRECT("B2:B"&COUNTA(B:B)-1))

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

Use this:

=SUM($B$3:INDEX(B:B,ROW()-1))

It is non volatile and will sum everything from B3 to the row in Column B above where the formula is placed.

Upvotes: 1

Related Questions