formula to add year to date figures
 

cells A1:L1 represent monthly expense totals from January to Dec. cell M1 is
the sum of the 12 months. I am trying to create a formula for cell N1 that
represents the year to date total of the previous year figures that I will be
getting form another worksheet. Here is an example...if there is a value
other than 0 in cells A1 and B1 and C1 but the value of cells D1:L1 are 0
then we know that the YTD formula in cell N1 should take 3 months of data
from Jan, Feb and March of the previous year. Then if cell D1 has a value
other than 0 the YTD formula will add 4 months and so on. I am not good with
this but the basics of a formula may look something like this =if(A1 does not
= 0 and B1:L1=0, then N1= WS2 A1, else if A1 and B1 do not = 0 and C1:L1=0,
then N1 = WS2 sum(A1:A2) or something like that. I think this is easier than
I am making it out to be. I appreciate your assistance with this formula.
--
Brian

formula to add year to date figures
 

One way:

N1: =SUM(OFFSET(PreviousYear!A1,0,0,1,COUNTIF(A1:L1," 0"))

Where PreviousYear is the name of last year's sheet.

In article ,
Brian wrote:

cells A1:L1 represent monthly expense totals from January to Dec. cell M1 is
the sum of the 12 months. I am trying to create a formula for cell N1 that
represents the year to date total of the previous year figures that I will be
getting form another worksheet. Here is an example...if there is a value
other than 0 in cells A1 and B1 and C1 but the value of cells D1:L1 are 0
then we know that the YTD formula in cell N1 should take 3 months of data
from Jan, Feb and March of the previous year. Then if cell D1 has a value
other than 0 the YTD formula will add 4 months and so on. I am not good with
this but the basics of a formula may look something like this =if(A1 does not
= 0 and B1:L1=0, then N1= WS2 A1, else if A1 and B1 do not = 0 and C1:L1=0,
then N1 = WS2 sum(A1:A2) or something like that. I think this is easier than
I am making it out to be. I appreciate your assistance with this formula.

formula to add year to date figures
 

something like:
=SUM(INDIRECT("'Sheet2'!R1C1:R1C"&COUNTIF(A1:L1,"< 0"),FALSE))
where Sheet2 is the name of the other sheet you want to pull data from.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Brian" wrote:

cells A1:L1 represent monthly expense totals from January to Dec. cell M1 is
the sum of the 12 months. I am trying to create a formula for cell N1 that
represents the year to date total of the previous year figures that I will be
getting form another worksheet. Here is an example...if there is a value
other than 0 in cells A1 and B1 and C1 but the value of cells D1:L1 are 0
then we know that the YTD formula in cell N1 should take 3 months of data
from Jan, Feb and March of the previous year. Then if cell D1 has a value
other than 0 the YTD formula will add 4 months and so on. I am not good with
this but the basics of a formula may look something like this =if(A1 does not
= 0 and B1:L1=0, then N1= WS2 A1, else if A1 and B1 do not = 0 and C1:L1=0,
then N1 = WS2 sum(A1:A2) or something like that. I think this is easier than
I am making it out to be. I appreciate your assistance with this formula.
--
Brian

formula to add year to date figures
 

would you mind looking at my post Feb2 to try to trouble shoot this again.
Thanks very much
--
Brian


"Luke M" wrote:

something like:
=SUM(INDIRECT("'Sheet2'!R1C1:R1C"&COUNTIF(A1:L1,"< 0"),FALSE))
where Sheet2 is the name of the other sheet you want to pull data from.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Brian" wrote:

cells A1:L1 represent monthly expense totals from January to Dec. cell M1 is
the sum of the 12 months. I am trying to create a formula for cell N1 that
represents the year to date total of the previous year figures that I will be
getting form another worksheet. Here is an example...if there is a value
other than 0 in cells A1 and B1 and C1 but the value of cells D1:L1 are 0
then we know that the YTD formula in cell N1 should take 3 months of data
from Jan, Feb and March of the previous year. Then if cell D1 has a value
other than 0 the YTD formula will add 4 months and so on. I am not good with
this but the basics of a formula may look something like this =if(A1 does not
= 0 and B1:L1=0, then N1= WS2 A1, else if A1 and B1 do not = 0 and C1:L1=0,
then N1 = WS2 sum(A1:A2) or something like that. I think this is easier than
I am making it out to be. I appreciate your assistance with this formula.
--
Brian