help in nested if formulas

[John48]

I keep a series of worksheets for weekly sales results that contain sales
results from Last year and entry points for comparison for sales for this
year eg
wk 1 Wk2 wk3 Wk4 Period to date
Year to date
TY LY % Ty LY % Ty Ly % TY Ly % Ty LY % Ty
LY %

Last years data is already populated from the previous year's sheet
automatically. My conumdrum is this. As I enter wk 1 this years sales, then
week 2 etc. they compare themselves to the already totalled period sales from
LY. I want the data in Period to date to add together as I enter in this
years sales. My attempt using IF nested formulas failed..here is what I tried.

=IF(B6>0,SUM(C6)),IF(E6>0,SUM(C6+F6)) and so on for wks 3 and 4.
Unfortunately because the IF value in the first formula is true as well as
the second as I enter the data, individually they are correct but the second
formula will not overide the first and by default the third (if i continued
on for wks 3 and 4) will not overide the second etc. Is there a way to have
each formula overide the previous to give a correct accumulated value for
last years sales to compare with the current data for this year???

 

[Dav]

Im not entirely clear what you require but perhaps

=SUMPRODUCT((MOD(COLUMN(C4:M4),3)=0)*(B4:L4>0)*(C4:M4))

if the column is c, f, i, l is the first condition

if the if the values one column to the left are >0 your condition, not
this range must be the same length as the previous range, but starts
and finishes one colum earlier

if both of the above conditions are true sum the corresponding values

Regards

Dav