formula to add year to date figures

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

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.
 
One way:

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

Where PreviousYear is the name of last year's sheet.
 
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.
 
would you mind looking at my post Feb2 to try to trouble shoot this again.
Thanks very much
 
Back
Top