I have a table with the following:
Name Quota-Date Quota
Ami 5/1/2010 75000
Ami 1/1/2012 100000
Ami 6/1/2014 150000
John 8/1/2014 0
John 4/1/2015 50000
Rick 5/1/2011 100000
(Dates are shown in American format: m/d/yyyy). "Quota Date" is the first month of the active new "Quota" next to it. E.g. Ami's quota is 75000 for each month between May 2010 and December 2011.
I need a formula to fetch the quota of a given person and a given month: the active quota of a person in every month. This needed formula is to calculate the third column of this table:
Name Month Quota
Ami 6/1/2010 75000
Ami 12/1/2011 75000
Ami 1/1/2012 100000
Ami 7/1/2014 150000
John 10/1/2014 0
John 4/1/2015 50000
I prefer not to maintain the first table sorted, but if it will make things significantly simpler, I would.
What would be the correct formula for "Quota" on the second table?
If your new data is in columns A-C and original data is also columns A-C in Sheet1, then enter this formula in B2:
=SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,MAX(IF((Sheet1!A:A=A2)*(Sheet1!B:B<=B2),Sheet1!B:B,"")))
This formula works well if you have only numbers in your 3rd column, but would be more complicated to make it working on text too.