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.

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.

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.

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.

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.