Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

EXCEL 2013 formula to sum on previous column

Posted on 2015-02-15
4
115 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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