Hi There,

Hopefully someone has more experience than me ans can help me out with a formula I am struggling to find!

I am tying to create a spreadsheet that allow me to look at growth rate based on sales over a 24 month period where 2 new customers come on board every month for 24 months they spend a different amount each month. Example month 1 I have 2 customers spend 1000 each, then in month 2 they spend 500 each but also in month 2 I have 2 new customers spending 1000 each so total would be 2 X 1000 and 2 X 500, then in month 3 I will have 2 new customers spending 1000, 2 who are in month 2 spending 500 and 2 who are in month 3 spending say 600 etc etc - Is there a formula or set up that could work for this. Appreciate any help. Have spend last few hours trying to work it out and failed!
Thanks
Ro

You need tables (every table on separate sheet is best solution):
Customers table where all customers are registered. Based on this table is a Named Range (e.g. nCustomers or lCustomers), used as source for data validation list to select customer in other tables;
Months table, where all months in format yyyymm from certain starting month up to current one are calculated. The formula will return empty string when it returns a month numer greater than current one. Based on this table, a Named Range (e.g. nMonths or lMonths) is created, which returns the list of not empty month numbers (you can use OFFSET function for this);
Now you can create a sales table, with columns for customer (data validation list), month (data validation list) and amount (general/numeric/money format);
And the last sheet will be for report, where at top you select the start month (using data validation list). My advice is, you define the cell where you select report month as Named Range (e.g. nReportMonth), so the reference to this cell doesn't change in formulas.
On report sheet you also create a 24 rows deep table with columns for month number (calculated from months table, starting from nRepMonth), and for amount (calculated from sales table using SUMIFS function [like =SUMIFS(SalesTable.Amount, SalesTable.MonthNumber, ReportTable.CurrentRowMonthNumber)]

Now, whenever you select any month in top of report sheet, in table there summary sales for 12 months starting from selected one are displayed.

Thank you so much for your help

Please mark thread solved ( see Thread tools)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post