excel 2010 - How to specify cell n cells to right of first non blank and then start row sum from that point -
i have worked out how find first non blank cell in row, return cell position, , using offset sum cell , next 11 cells (some of blank), =sum(offset(a11,0,by11,1,12)).
a11 customer reference number , by11 contains formula return position in row of first non blank cell (my starting position).
need able start @ next cell (after 12 have been summed in formula above) , sum 12 cells along, , again start @ cell , sum 12 cells along. summing values of customers spend in first year, second year, third year etc of course of customers spends start in different month each other. thinking extension of offset formula cant work out how it. appreciated.
i have worked out how answer own question if finds help: inserted 12 blank columns each set of 12 columns needed sum (to prevent circular summing) , inserted +12 after by11 part of formula. adding in enough empty columns 5 years worth of summing means year 1 totals have formula =sum(offset(a17,0,du17,1,12)) , year 2 totals =sum(offset($a17,0,du17+12,1,12)) , year 3 totals =sum(offset($a17,0,du17+24,1,12)) seems working. problem have keep inserting empty column everytime add in new months values. know how stop sum @ column if part of 12 cells have been summed i.e. don't want sum further column bj. thanks
Comments
Post a Comment