Spreadsheet: 732906d1621306916-formula-for-returning-sum-of-values-for-three-months-depending-on-the-end-date-of-a-cell-example-1-
Sheet: Competitor

Company | Metric | Fiscal Period | Calendar Ending Date | Segment | Value |  |  |  |  |  | [NAME] for returning sum of values for three months depending on the end date of a cell
Competitor A | EPS | Q4 FY20 | 2020-05-31 00:00:00 | Beverage | 104.2
Competitor A | Net Sales | Q4 FY20 | 2020-05-31 00:00:00 | Snack | 86.1
Competitor A | Operating Income | Q4 FY20 | 2020-05-31 00:00:00 | Bread | 47.20000000000002 |  |  |  |  |  | I have a large data table with several companies and different metrics per quarter like net sales, EBITDA, EPS, etc. There is a column for company, fiscal year, fiscal quarter, calendar ending date, metric name, and value. There are more columns, but I believe these will be the only ones relevant for now. Not every company follows the calendar year for their fiscal year, and not every company follows the same fiscal year as my company does. I need a formula that will show the value of three summed months for a metric (EPS, for example) that will follow the quarter for a specific company.
Competitor A | EBITDA | Q4 FY20 | 2020-05-31 00:00:00 | Dairy | 102.8
Competitor A | Tax | Q4 FY20 | 2020-05-31 00:00:00 | Pork | 117 |  |  |  |  |  | So, I believe that the calendar ending date will come into play. Depending on the month listed in this cell, the formula will count back to include the three previous months. It will use that time frame to look up the values for those three months in my company and add them together. (This will either come from a separate file or a separate worksheet.) Then it will return that summation of those three months values in the new column.
Competitor A | Gross Profit | Q4 FY20 | 2020-05-31 00:00:00 | Beverage | 106.6
Competitor A | EPS | Q3 FY20 | 2020-02-29 00:00:00 | Snack | 77.1 |  |  |  |  |  | Please see attached. I have edited the file so that fiscal year and quarter are now in the same column.
Competitor A | Net Sales | Q3 FY20 | 2020-02-29 00:00:00 | Bread | 81.60000000000002
Competitor A | Operating Income | Q3 FY20 | 2020-02-29 00:00:00 | Dairy | 145.5 |  |  |  |  |  | You can see four different competitors on the first sheet, and my company 'Home' on the second sheet. You will also see that no fiscal quarter end is alike except between competitors A and D.
Competitor A | EBITDA | Q3 FY20 | 2020-02-29 00:00:00 | Pork | 136.5 |  |  |  |  |  | Please think of this example to further understand the kind of formula I am needing:
Competitor A | Tax | Q3 FY20 | 2020-02-29 00:00:00 | Beverage | 114.7
Competitor A | Gross Profit | Q3 FY20 | 2020-02-29 00:00:00 | Snack | 80.352 |  |  |  |  |  | I need to add a column to the competitors sheet. Let's look at Competitor B. I want to look up what the matching metric is for each segment for Competitor B's third quarter.
Competitor A | EPS | Q2 FY20 | 2019-11-30 00:00:00 | Bread | 58.211 |  |  |  |  |  | This quarter runs from July to September. If you look at the 'Home' tab, you will see that Competitor B's third quarter includes the last month of our third quarter and the
Competitor A | Net Sales | Q2 FY20 | 2019-11-30 00:00:00 | Dairy | 40.363 |  |  |  |  |  | first two months of our fourth quarter. I want the monthly values to be added together and this be what is returned on the 'Competitors' tab.
Competitor A | Operating Income | Q2 FY20 | 2019-11-30 00:00:00 | Pork | 66.61
Competitor A | EBITDA | Q2 FY20 | 2019-11-30 00:00:00 | Beverage | 139.607 |  |  |  |  |  | In cell G26, I want 203.74 to be returned. The formula should find that the months of the Home company that fall within Competitor B's third quarter are July through September.
Competitor A | Tax | Q2 FY20 | 2019-11-30 00:00:00 | Snack | 88.267 |  |  |  |  |  | Then it should look for the EPS values that fall within the correct segment (in this case, bread) and sum the values that fit all criteria.
Competitor A | Gross Profit | Q2 FY20 | 2019-11-30 00:00:00 | Bread | 140.671
Competitor B | EPS | Q4 FY20 | 2020-12-31 00:00:00 | Dairy | 52.943 |  |  |  |  |  | The function will likely not work for many instances as this data set is rather small and random. I simply cannot share my original data and also cannot spend hours creating a fake data set.
Competitor B | Net Sales | Q4 FY20 | 2020-12-31 00:00:00 | Pork | 88.21699999999998 |  |  |  |  |  | I do hope this gives you a better understanding. Feel free to edit things within this file accordingly to allow a formula to better show functionality.
Competitor B | Operating Income | Q4 FY20 | 2020-12-31 00:00:00 | Beverage | 102.815
Competitor B | EBITDA | Q4 FY20 | 2020-12-31 00:00:00 | Snack | 145.632
Competitor B | Tax | Q4 FY20 | 2020-12-31 00:00:00 | Bread | 39.42999999999999
Competitor B | Gross Profit | Q4 FY20 | 2020-12-31 00:00:00 | Dairy | 48.191
Competitor B | EPS | Q3 FY20 | 2020-09-30 00:00:00 | Bread | 127
Competitor B | Net Sales | Q3 FY20 | 2020-09-30 00:00:00 | Beverage | 131
Competitor B | Operating Income | Q3 FY20 | 2020-09-30 00:00:00 | Snack | 227
Competitor B | EBITDA | Q3 FY20 | 2020-09-30 00:00:00 | Bread | 215
Competitor B | Tax | Q3 FY20 | 2020-09-30 00:00:00 | Dairy | 156
Competitor B | Gross Profit | Q3 FY20 | 2020-09-30 00:00:00 | Pork | 232
Competitor B | EPS | Q2 FY20 | 2020-06-30 00:00:00 | Beverage | 249
Competitor B | Net Sales | Q2 FY20 | 2020-06-30 00:00:00 | Snack | 199
Competitor B | Operating Income | Q2 FY20 | 2020-06-30 00:00:00 | Bread | 133
Competitor B | EBITDA | Q2 FY20 | 2020-06-30 00:00:00 | Dairy | 187
Competitor B | Tax | Q2 FY20 | 2020-06-30 00:00:00 | Pork | 139
Competitor B | Gross Profit | Q2 FY20 | 2020-06-30 00:00:00 | Beverage | 51.734
Competitor C | EPS | Q4 FY20 | 2021-01-31 00:00:00 | Snack | 75.19399999999999
Competitor C | Net Sales | Q4 FY20 | 2021-01-31 00:00:00 | Bread | 89.65899999999999
Competitor C | Operating Income | Q4 FY20 | 2021-01-31 00:00:00 | Dairy | 92.28800000000004
Competitor C | EBITDA | Q4 FY20 | 2021-01-31 00:00:00 | Pork | 83.388
Competitor C | Tax | Q4 FY20 | 2021-01-31 00:00:00 | Beverage | 78.88
Competitor C | Gross Profit | Q4 FY20 | 2021-01-31 00:00:00 | Snack | 2354.9
Competitor C | EPS | Q3 FY20 | 2020-10-31 00:00:00 | Bread | 1318.9
Competitor C | Net Sales | Q3 FY20 | 2020-10-31 00:00:00 | Dairy | 1676.5
Competitor C | Operating Income | Q3 FY20 | 2020-10-31 00:00:00 | Pork | 1926
Competitor C | EBITDA | Q3 FY20 | 2020-10-31 00:00:00 | Beverage | 1902.199999999999
Competitor C | Tax | Q3 FY20 | 2020-10-31 00:00:00 | Snack | 1721.7
Competitor C | Gross Profit | Q3 FY20 | 2020-10-31 00:00:00 | Bread | 2015.3
Competitor C | EPS | Q2 FY20 | 2020-07-31 00:00:00 | Dairy | 1859.7
Competitor C | Net Sales | Q2 FY20 | 2020-07-31 00:00:00 | Pork | 1858.3
Competitor C | Operating Income | Q2 FY20 | 2020-07-31 00:00:00 | Beverage | 2096.8
Competitor C | EBITDA | Q2 FY20 | 2020-07-31 00:00:00 | Snack | 2003.5
Competitor C | Tax | Q2 FY20 | 2020-07-31 00:00:00 | Bread | 1945.113
Competitor C | Gross Profit | Q2 FY20 | 2020-07-31 00:00:00 | Dairy | 1916.014
Competitor D | EPS | Q4 FY20 | 2020-05-31 00:00:00 | Pork | 2010.977
Competitor D | Net Sales | Q4 FY20 | 2020-05-31 00:00:00 | Beverage | 1857.263
Competitor D | Operating Income | Q4 FY20 | 2020-05-31 00:00:00 | Snack | 2007.79
Competitor D | EBITDA | Q4 FY20 | 2020-05-31 00:00:00 | Bread | 1959.032
Competitor D | Tax | Q4 FY20 | 2020-05-31 00:00:00 | Dairy | 1962.34
Competitor D | Gross Profit | Q4 FY20 | 2020-05-31 00:00:00 | Pork | 1832.997
Competitor D | EPS | Q3 FY20 | 2020-02-29 00:00:00 | Beverage | 1837.765
Competitor D | Net Sales | Q3 FY20 | 2020-02-29 00:00:00 | Snack | 1904.096
Competitor D | Operating Income | Q3 FY20 | 2020-02-29 00:00:00 | Bread | 1991.369
Competitor D | EBITDA | Q3 FY20 | 2020-02-29 00:00:00 | Dairy | 1872.021
Competitor D | Tax | Q3 FY20 | 2020-02-29 00:00:00 | Pork | 1875.595
Competitor D | Gross Profit | Q3 FY20 | 2020-02-29 00:00:00 | Beverage | 2704.2
Competitor D | EPS | Q2 FY20 | 2019-11-30 00:00:00 | Snack | 4372.3
Competitor D | Net Sales | Q2 FY20 | 2019-11-30 00:00:00 | Bread | 1395.7
Competitor D | Operating Income | Q2 FY20 | 2019-11-30 00:00:00 | Dairy | 13776.5
Competitor D | EBITDA | Q2 FY20 | 2019-11-30 00:00:00 | Pork | 1721.7
Competitor D | Tax | Q2 FY20 | 2019-11-30 00:00:00 | Beverage | 2015.3
Competitor D | Gross Profit | Q2 FY20 | 2019-11-30 00:00:00 | Snack | 1859.7