formula to add year to date figures

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.
 
J

JE McGimpsey

One way:

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

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

Luke M

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.
 
B

Brian

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top