Solved

EXCEL 2013 formula to sum on previous column

Posted on 2015-02-15
4
103 Views
Last Modified: 2015-02-15
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.

Sum on WTD to calculate YTD
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
Comment
Question by:Stephen Kairys
  • 2
4 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40610869
Can you post your sample workbook ?
gowlfow
0
 
LVL 14

Accepted Solution

by:
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

by:Stephen Kairys
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

by:Stephen Kairys
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now