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?

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?

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?

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?