Hello, I am using the following formula to work out the sales value for a customers first 12 months with us and then each consecutive 12 months after that. Each month is in the column header, and the customer reference is in column A. The data under each month is the total value of sales for that month. The formula finds the first non blank value for the customer and sums that cell plus the next 11 cells giving the first years totals
where A17 is the customer reference number and DU17 contains a formula to return the position in the row of the first non blank cell (my starting position).
In the following column I have added the formula that follows to calculate the following 12 months for year 2
and the same for years 3,4, & 5 in the following 3 columns by increasing the '+12' to the appropriate number of months (24,36,48). This works fine however I have had to insert 25 blank columns between the end of the data and my formula columns in order to prevent circular references from occurring (to give enough room for all 5 years to be added to the latest start date. This means that I will have to keep inserting an empty column every time I add in the values for a new month. 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.
Bookmarks