Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to sum columns based on # in Cell A1
I have columns 5-16 (E:P) 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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to sum columns based on # in Cell A1
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:P) 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to sum columns based on # in Cell A1
=SUM(OFFSET(E5,0,0,1,A1-4))
-- Jim "Karin" wrote in message ... |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:P) 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to sum columns based on # in Cell A1
=SUM(E5:OFFSET(E5,0,MOD(MONTH(A1)-5,12)))
-- David Biddulph "Karin" wrote in message ... I have columns 5-16 (E:P) 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to sum columns based on # in Cell A1
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 "Karin" wrote in message ... |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:P) 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to sum columns based on # in Cell A1
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. -- David Biddulph "Jim Rech" wrote in message ... =SUM(OFFSET(E5,0,0,1,A1-4)) "Karin" wrote in message ... |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:P) 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to sum columns based on # in Cell A1
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 "Karin" wrote in message ... | 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 | "Karin" wrote in message | ... | |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:P) 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! | | | |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to sum columns based on # in Cell A1
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. "David Biddulph" wrote: 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. -- David Biddulph "Jim Rech" wrote in message ... =SUM(OFFSET(E5,0,0,1,A1-4)) "Karin" wrote in message ... |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:P) 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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to sum columns based on # in Cell A1
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.) "Jim Rech" wrote: 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 "Karin" wrote in message ... | 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 | "Karin" wrote in message | ... | |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:P) 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! | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I have a formula result based on multiple criteria/columns | New Users to Excel | |||
Is there a way to hide worksheets and/or rows/columns based on information enter into a particular cell of range of cells? | New Users to Excel | |||
Automatically resize columns based on new formula result | Excel Discussion (Misc queries) | |||
formula to count based on data in two different cells/columns | Excel Discussion (Misc queries) | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions |