If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel sum with variable start column
I have over 10,000 rows of data, some of it in columns corresponding to
years. I want to make a sum in each row that starts with a different/variable year, and sums to the present. Can I use a sum equation to do this? Asked a different way, how to I tell the sum where to start, when the starting column is not fixed? |
#2
|
|||
|
|||
Excel sum with variable start column
Are you familiar with SUMIF? For example, =SUMIF(A:A,"=" &
DATE(2008,6,1),B:B) would inspect all cells in column A for dates on/after 6/1/08, and add up the corresponding cells from column B. You could use the same idea to select a specif date range. =SUMIF(A:A,"=" & DATE(2008,1,1),B:B)-SUMIF(A:A,"=" & DATE(2009,1,1),B:B) would add up all those from 2008. "Excel semi-smart" wrote: I have over 10,000 rows of data, some of it in columns corresponding to years. I want to make a sum in each row that starts with a different/variable year, and sums to the present. Can I use a sum equation to do this? Asked a different way, how to I tell the sum where to start, when the starting column is not fixed? |
#3
|
|||
|
|||
Excel sum with variable start column
Thanks, That is a great answer. Unfortunately the columns are not cleanly labeled with a date. The column with the year also has text, as in "Units 2008", so I would have to strip out the text. I know how to do that, but I have assumed that with a very large file that kind of complexity in the equation would really be a problem in file size and/or calucualtion time. Perhaps not? I thought, in an ealier verison of Excel, it was possible to write a sum where you could specify how many columns back, as in Sum(-3:-1), but I don't recall what the syntax was or whether it is still valid. Ring a bell? "bapeltzer" wrote: Are you familiar with SUMIF? For example, =SUMIF(A:A,"=" & DATE(2008,6,1),B:B) would inspect all cells in column A for dates on/after 6/1/08, and add up the corresponding cells from column B. You could use the same idea to select a specif date range. =SUMIF(A:A,"=" & DATE(2008,1,1),B:B)-SUMIF(A:A,"=" & DATE(2009,1,1),B:B) would add up all those from 2008. "Excel semi-smart" wrote: I have over 10,000 rows of data, some of it in columns corresponding to years. I want to make a sum in each row that starts with a different/variable year, and sums to the present. Can I use a sum equation to do this? Asked a different way, how to I tell the sum where to start, when the starting column is not fixed? |
#4
|
|||
|
|||
Excel sum with variable start column
Well, you can use 'index' to reference a particular entry in your list.
Again assuming that your label, 'Units 2008' is in column A and the data in column B, you can calculate the sum beginning with the labeled cell with =SUM(INDEX(B:B,MATCH("Units 2008",A:A,0)):B24) The MATCH finds the row containing your starting label. INDEX then uses that to reference the start of the data to be added. I've shown B24 as the end of the data to add, but that could be identified in the same way as the starting point. "Excel semi-smart" wrote: Thanks, That is a great answer. Unfortunately the columns are not cleanly labeled with a date. The column with the year also has text, as in "Units 2008", so I would have to strip out the text. I know how to do that, but I have assumed that with a very large file that kind of complexity in the equation would really be a problem in file size and/or calucualtion time. Perhaps not? I thought, in an ealier verison of Excel, it was possible to write a sum where you could specify how many columns back, as in Sum(-3:-1), but I don't recall what the syntax was or whether it is still valid. Ring a bell? "bapeltzer" wrote: Are you familiar with SUMIF? For example, =SUMIF(A:A,"=" & DATE(2008,6,1),B:B) would inspect all cells in column A for dates on/after 6/1/08, and add up the corresponding cells from column B. You could use the same idea to select a specif date range. =SUMIF(A:A,"=" & DATE(2008,1,1),B:B)-SUMIF(A:A,"=" & DATE(2009,1,1),B:B) would add up all those from 2008. "Excel semi-smart" wrote: I have over 10,000 rows of data, some of it in columns corresponding to years. I want to make a sum in each row that starts with a different/variable year, and sums to the present. Can I use a sum equation to do this? Asked a different way, how to I tell the sum where to start, when the starting column is not fixed? |
Thread Tools | |
Display Modes | |
|
|