Solved

# EXCEL 2013 formula to sum on previous column

Posted on 2015-02-15
103 Views
I have a spreadsheet that has columns for each day of the week, as well as WeekToDate (WTD) and YTD totals. The WTD column is calculated based on the sum of the Monday - Sunday columns.

WTD=Column I
YTD = Column J

The data in col. J here is manually entered.

What I'd like to do is to have the YTD column represent the sum of the WTD from row 2 (the first data row) to the current row.

e.g. J5 would be SUM(I2:I4)
e.g. J6 would be SUM(I2:I5)

In general terms:
J<row#> would be SUM(I2:I<row#>)

Is there a (simple) formula for this calculation?

Thanks,
Steve
0
Question by:Stephen Kairys
• 2

LVL 29

Expert Comment

ID: 40610869
Can you post your sample workbook ?
gowlfow
0

LVL 14

Accepted Solution

ThomasMcA2 earned 500 total points
ID: 40610954
Use the \$ symbol to lock the cell references. For example, the formula =SUM(I2:I4) for J5 becomes =SUM(I3:I5) when it is copied and pasted into J6. To fix that, change your formula for J5 to =SUM(\$I\$2:I4). The \$ signs stop Excel from changing the column (\$I) and row (\$2) when being pasted. Once you change J5, you can copy and paste it down the J column.
0

LVL 4

Author Comment

ID: 40610960
Thomas,
To confirm:

SUM(\$I\$2:I4)

The dollar signs are before and after the first "I"? Did you mean \$I2:\$I4 instead?

Thanks,
Steve
0

LVL 4

Author Closing Comment

ID: 40610970
Thomas, please disregard my last comment. I tried your method and it worked! Thank you.

Gowlfow - Thanks for wanting to help, but the workbook has some personal info in it that I could not share.
0

## Featured Post

### Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…