I have worked out how to find the first non blank cell in a row, return the cell position, and then using OFFSET sum that cell and the next 11 cells (some of which will be blank), =SUM(OFFSET(A11,0,BY11,1,12)). where A11 is the customer reference number and BY11 contains a formula to return the position in the row of the first non blank cell (my starting position).
I now need to be able to start at the next cell (after the 12 that have already been summed in the formula above) and sum 12 cells along, and again start at that cell and sum 12 cells along. I am summing the values of a customers spend in their first year, second year, third year etc but of course all of the customers spends start in a different month from each other. I am thinking that it will be an extension of the OFFSET formula but I cant work out how to do it. Any help would be greatly appreciated.
1 Answers
I have worked out how to answer my own question if anyone finds it a help: I just inserted 12 blank columns for each set of 12 columns that I needed to sum (to prevent circular summing) and inserted +12 after the BY11 part of the formula. Adding in enough empty columns for 5 years worth of summing means that for year 1 totals I now have the formula =SUM(OFFSET(A17,0,DU17,1,12)) and for year 2 totals =SUM(OFFSET($A17,0,DU17+12,1,12)) and for year 3 totals =SUM(OFFSET($A17,0,DU17+24,1,12)) which seems to be working. The problem now is that I will have to keep inserting an empty column everytime I add in a new months values. Does anyone know how to stop the sum at a certain column even if only part of the 12 cells have been summed i.e. I don't want to sum any further than column BJ. Thanks
