Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum year to date tables
I want to sum revenue's depending on the datespecified in a Cell (for example
A1= march-07). I have the full year plan on a separate sheet. I want to be able to specify the month and the answer come back with the sum of the month up to that date. I have a separate sheet with the plan for the year "year plan". the first row is the date jan-07 feb 07 mar-07..ect) the second row is the revenue figures for that month plan. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum year to date tables
Hi,
Hard to say without more details but something like this perhaps: =SUMPRODUCT((MONTH(B1:B100)=1)*(MONTH(B1:B100)<=M ONTH(A1)),C1:C100) this will sum the values in column C if the date in column B is from Jan up to the month of the date in A1. HTH Jean-Guy "Tom" wrote: I want to sum revenue's depending on the datespecified in a Cell (for example A1= march-07). I have the full year plan on a separate sheet. I want to be able to specify the month and the answer come back with the sum of the month up to that date. I have a separate sheet with the plan for the year "year plan". the first row is the date jan-07 feb 07 mar-07..ect) the second row is the revenue figures for that month plan. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum year to date tables
i am not so familiar with your question yet i'll try to help...
=SUMPRODUCT(--(MONTH(sheet2!A1:L1)<=MONTH(A1)),sheet2!A2:L2) e.g. sheet2!A1:L1 : JAN-07 to DEC-07 sheet2!A2:L2 : REVENUES PER MONTH just maybe regards -- ***** birds of the same feather flock together.. "Tom" wrote: I want to sum revenue's depending on the datespecified in a Cell (for example A1= march-07). I have the full year plan on a separate sheet. I want to be able to specify the month and the answer come back with the sum of the month up to that date. I have a separate sheet with the plan for the year "year plan". the first row is the date jan-07 feb 07 mar-07..ect) the second row is the revenue figures for that month plan. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum year to date tables
Works perfect.
Thanks for this. Tom "pinmaster" wrote: Hi, Hard to say without more details but something like this perhaps: =SUMPRODUCT((MONTH(B1:B100)=1)*(MONTH(B1:B100)<=M ONTH(A1)),C1:C100) this will sum the values in column C if the date in column B is from Jan up to the month of the date in A1. HTH Jean-Guy "Tom" wrote: I want to sum revenue's depending on the datespecified in a Cell (for example A1= march-07). I have the full year plan on a separate sheet. I want to be able to specify the month and the answer come back with the sum of the month up to that date. I have a separate sheet with the plan for the year "year plan". the first row is the date jan-07 feb 07 mar-07..ect) the second row is the revenue figures for that month plan. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum year to date tables
Thanks for the help,
It answered my question perfectly. regards and have a good day, Tom "driller" wrote: i am not so familiar with your question yet i'll try to help... =SUMPRODUCT(--(MONTH(sheet2!A1:L1)<=MONTH(A1)),sheet2!A2:L2) e.g. sheet2!A1:L1 : JAN-07 to DEC-07 sheet2!A2:L2 : REVENUES PER MONTH just maybe regards -- ***** birds of the same feather flock together.. "Tom" wrote: I want to sum revenue's depending on the datespecified in a Cell (for example A1= march-07). I have the full year plan on a separate sheet. I want to be able to specify the month and the answer come back with the sum of the month up to that date. I have a separate sheet with the plan for the year "year plan". the first row is the date jan-07 feb 07 mar-07..ect) the second row is the revenue figures for that month plan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
holiday dates | Excel Worksheet Functions | |||
How to return a value between date ranges | Excel Worksheet Functions | |||
List First Friday's Date of the year | Excel Discussion (Misc queries) | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) |