Auto Sum a Range of Cells based on the date
 

Hello,

I have a table with each column titled by the month, and in row 1 I have a
range of data. I would like to have a formula that automatically sums up the
range up to the current month without having to enter in a new formula any
month. Is this possible? I imagine it would require the TODAY() function
but after that I am lost.

Example:

Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07
1.0 2.0 2.0 2.0 1.0 1.0 1.0 2.0

Today I would want to add up the Nov-Jan colums to get (5), but next month
when I open the same spreadsheet I would like that same formula to add up the
Nov-Feb columns to get (7)
Thanks!
Joe

Auto Sum a Range of Cells based on the date
 

There are a few approaches

Using your posted data in A1:H2
and
If the first row of data contains actual dates that are the 1st of the month

Try this:
A3: =SUM(A2:INDEX(A2:H2,1,MATCH(TODAY(),A1:H1,1)))

Is that something you can work with?

Or.... if your data has a different structure, please share it.
***********
Regards,
Ron

XL2002, WinXP


"jcpotwor" wrote:

Hello,

I have a table with each column titled by the month, and in row 1 I have a
range of data. I would like to have a formula that automatically sums up the
range up to the current month without having to enter in a new formula any
month. Is this possible? I imagine it would require the TODAY() function
but after that I am lost.

Example:

Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07
1.0 2.0 2.0 2.0 1.0 1.0 1.0 2.0

Today I would want to add up the Nov-Jan colums to get (5), but next month
when I open the same spreadsheet I would like that same formula to add up the
Nov-Feb columns to get (7)
Thanks!
Joe

Auto Sum a Range of Cells based on the date
 

Hi Ron,

It works perfectly - THANK YOU SO MUCH !!!!!

"Ron Coderre" wrote:

There are a few approaches

Using your posted data in A1:H2
and
If the first row of data contains actual dates that are the 1st of the month

Try this:
A3: =SUM(A2:INDEX(A2:H2,1,MATCH(TODAY(),A1:H1,1)))

Is that something you can work with?

Or.... if your data has a different structure, please share it.
***********
Regards,
Ron

XL2002, WinXP


"jcpotwor" wrote:

Hello,

I have a table with each column titled by the month, and in row 1 I have a
range of data. I would like to have a formula that automatically sums up the
range up to the current month without having to enter in a new formula any
month. Is this possible? I imagine it would require the TODAY() function
but after that I am lost.

Example:

Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07
1.0 2.0 2.0 2.0 1.0 1.0 1.0 2.0

Today I would want to add up the Nov-Jan colums to get (5), but next month
when I open the same spreadsheet I would like that same formula to add up the
Nov-Feb columns to get (7)
Thanks!
Joe

Auto Sum a Range of Cells based on the date
 

Hi,

Another way:

=SUMPRODUCT(--(A1:H1<=TODAY()),A2:H2)

Cheers!
Jean-Guy

"jcpotwor" wrote:

Hello,

I have a table with each column titled by the month, and in row 1 I have a
range of data. I would like to have a formula that automatically sums up the
range up to the current month without having to enter in a new formula any
month. Is this possible? I imagine it would require the TODAY() function
but after that I am lost.

Example:

Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07
1.0 2.0 2.0 2.0 1.0 1.0 1.0 2.0

Today I would want to add up the Nov-Jan colums to get (5), but next month
when I open the same spreadsheet I would like that same formula to add up the
Nov-Feb columns to get (7)
Thanks!
Joe