Hi jess,- attached is a very small cut off of a very large data set I have - I have spent an hour on this trying to do a quick formula and have no been able to do it. Im very frustrated and am not looking at this clearly. - yes, there are manual multi-step ways around creating additional tables with date mapping to allow sumifs to work, but given size of data i would prefer not. looking for efficient way. Again, remembering the data set I have is 100x larger than thisGoal: - Populate the yellow cells using as much automated formulas as possible.Challange is that the retrieve columns need to be dynamic for each person because they all start on different datesAny ideas?

Customer:replied 4 months ago.

My best theory thus far is a combination SUMIFS / Index / Match - but I can't seem to put it together

The real file goes well past column Q. to about column AS or so.. I I only provided this smaller dataset as an example. Im attaching the true file. Again, the goal is just finding a way to have the SUMIFS be dynamic based on column header

Thank you for the sample file. How "dynamic" is your column header? In the first sample you gave you with the yellow columns, the headers are Months 0-3, Months 4-6, what are they representing? Your actual data are Month-Year like Jan-15, Feb-15 which means January 2015 and February 2015 respectively.

Hi JessLet me step back any give you a high level goals again - The data has employee start date, and then monthly periods of bookings - Im trying to figure out on average how much the (on average) a sales person does in bookings during their first 12 quarters with the companyTherefore, Im trying to write a formula that considers their start date, and also the date in the column heading to report out the results of their first 12 quarters (36 months) of employment.

Let us clarify few things first on your actual data, let us take row 5 (Rep_1). His start date is 2009 but the actual data starts 2013. So the valid columns will be J (first month) through AS (36 month).

For row 7 (rep_5), the start date is 2015 but the actual data is 2013. So valid columns will be AG (first month, March 2015) and AS as the 13th month?

In the first sample you gave me, your HEADERS are Months 0-3, Months 4-6, etc. So you are grouping them in quarters. So you need to go until Months 34-36?

Jess,It really doesn't matter, I just need the foundation for the formula and I can tweak after that....You can build it with months or quarters and I can adjustif you have questions - please call my cell at(###) ###-####

Hi PC Guy,Please check your work here: if I look at row 6, for example, it totals 398k... which is the sum of the entire row..To repeat the requirement again: There needs to be a delineation between employee start date and the date shown in row 4. Your formula as written is simply summing the entire row

trust me the formula is working as requested, double check your data remember it is starting dynamically based on the start month and year, and then adding for upto 3 years from that date according to your requirement. This is all based on start date and the dates across the top of your columns.