Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum based on two selections - months
Afternoon from Sunny RSA,
I have a table - Jul (Column P) to Jun (Column AA) with values in each column for applicable month. Each row has information pertaining to an event i.e volumes delivered in row 13 Now I have two drop downs with the months listed. In cell D5 = Jul and Cell F5 = Sep Now I need a formula that will go to the table, find Jul and use as the starting point, now go and find the month selected in F5 (in this case Sep) and sum the values between the two month to give me a Year To Date value in cell J13. Thanks in advance for your answer. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum based on two selections - months
Try this. I assuming your mean by Between to add July, August, and
September. I'm not using the table heders instead using an offset based on the Month Number. I am converting each month name into a Serial Date (1-Jul-08) and getting the month number. Ijust am using the offset function and computing the column based on the first month Jul being in column P. =SUM(OFFSET(P13,0,MOD(MONTH(DATEVALUE("1-"&D5&"-2008"))-7,12),1,MOD(MONTH(DATEVALUE("1-"&F5&"-2008"))-6,12))) "Sunnyskies" wrote: Afternoon from Sunny RSA, I have a table - Jul (Column P) to Jun (Column AA) with values in each column for applicable month. Each row has information pertaining to an event i.e volumes delivered in row 13 Now I have two drop downs with the months listed. In cell D5 = Jul and Cell F5 = Sep Now I need a formula that will go to the table, find Jul and use as the starting point, now go and find the month selected in F5 (in this case Sep) and sum the values between the two month to give me a Year To Date value in cell J13. Thanks in advance for your answer. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum based on two selections - months
Thanks Joel will test.
"Joel" wrote: Try this. I assuming your mean by Between to add July, August, and September. I'm not using the table heders instead using an offset based on the Month Number. I am converting each month name into a Serial Date (1-Jul-08) and getting the month number. Ijust am using the offset function and computing the column based on the first month Jul being in column P. =SUM(OFFSET(P13,0,MOD(MONTH(DATEVALUE("1-"&D5&"-2008"))-7,12),1,MOD(MONTH(DATEVALUE("1-"&F5&"-2008"))-6,12))) "Sunnyskies" wrote: Afternoon from Sunny RSA, I have a table - Jul (Column P) to Jun (Column AA) with values in each column for applicable month. Each row has information pertaining to an event i.e volumes delivered in row 13 Now I have two drop downs with the months listed. In cell D5 = Jul and Cell F5 = Sep Now I need a formula that will go to the table, find Jul and use as the starting point, now go and find the month selected in F5 (in this case Sep) and sum the values between the two month to give me a Year To Date value in cell J13. Thanks in advance for your answer. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
total amount based on selections of 1-9, 10-19 and 20-30 | Excel Discussion (Misc queries) | |||
Set an array on based on duration of months | Excel Worksheet Functions | |||
Calculating revenue based on accounting months | Excel Discussion (Misc queries) | |||
Top Five selections based on sum of items meeting criteria | Excel Worksheet Functions | |||
sum automatically from last 12 months based on current date | Excel Worksheet Functions |