Formula to sum columns based on # in Cell A1

[Karin]

I have columns 5-16 (E) filled with monthly data.
Column 5 (E) contains May, column 6 (F) June, etc.
I
n Cell A1 I have the current month (7 for July, 8 for Aug, etc.). Cell A1
has the formula =now() in it and is formatted as "m" so it only shows 7 for
July, which corresponds with column 7 (G).

I want to sum cells E5 through ?5 based on the number in cell A1.
For July the sum would be E5:G5, for August it would be E5:H5, etc.)
What fomula would I use to create the sum based on the data in cell A1?
TIA!

 

[Karin]

I made a slight change (Cell A1 is now a hard coded number, not a date
formatted to look like a number).

 

[Jim Rech]

=SUM(OFFSET(E5,0,0,1,A1-4))

--
Jim
|I made a slight change (Cell A1 is now a hard coded number, not a date
| formatted to look like a number).
|
| "Karin" wrote:
|
| > I have columns 5-16 (E) filled with monthly data.
| > Column 5 (E) contains May, column 6 (F) June, etc.
| > In Cell A1 I have the current month (7 for July, 8 for Aug, etc.).
| >
| > I want to sum cells E5 through ?5 based on the number in cell A1.
| > For July the sum would be E5:G5, for August it would be E5:H5, etc.)
| > What fomula would I use to create the sum based on the data in cell A1?
| > TIA!

 

[David Biddulph]

=SUM(E5:OFFSET(E5,0,MOD(MONTH(A1)-5,12)))

 

[Karin]

Thank you. Now let's get more complex. Is there anyway to return this
formula in a Vlookup? I'm looking up a name in the range YTD, then I want to
do this formula for the row that the name is in.

 

[David Biddulph]

OK for months from 5 to 12, but not for 1 to 4 inclusive, Jim.
You'll need the MOD() function as in my earlier answer before the question
changed.

 

[Jim Rech]

Thanks, David, for pointing out that I ignored months past December.

Karen, play with something like this:

=SUM(OFFSET(indirect(vlookup(...)),0,0,1,MOD(A1-4,12)))

Use VLOOKUP to get the name of the range and INDIRECT to make Excel find the
range with that name.

--
Jim
| Thank you. Now let's get more complex. Is there anyway to return this
| formula in a Vlookup? I'm looking up a name in the range YTD, then I want
to
| do this formula for the row that the name is in.
|
| "Jim Rech" wrote:
|
| > =SUM(OFFSET(E5,0,0,1,A1-4))
| >
| > --
| > Jim
| > | > |I made a slight change (Cell A1 is now a hard coded number, not a date
| > | formatted to look like a number).
| > |
| > | "Karin" wrote:
| > |
| > | > I have columns 5-16 (E) filled with monthly data.
| > | > Column 5 (E) contains May, column 6 (F) June, etc.
| > | > In Cell A1 I have the current month (7 for July, 8 for Aug, etc.).
| > | >
| > | > I want to sum cells E5 through ?5 based on the number in cell A1.
| > | > For July the sum would be E5:G5, for August it would be E5:H5, etc.)
| > | > What fomula would I use to create the sum based on the data in cell
A1?
| > | > TIA!
| >
| >
| >

 

[Karin]

Actually, months past December are 13, 14, 15, 16 because I am referencing
the Column number that the month falls in. (It just happened that May, which
is the start of our fiscal year, fell in the 5th column.) But it's good to
have a solution for anyone using typical 1-12 numbers.

 

[Karin]

Jim, Thanks for the formula, but I can't seem to get it to work. I reposted
this question as a new question (Lookup and Calculate Formula) and I'll post
your answer there. (It seemed to need its own question as it went beyond the
scope of my original question.)