Formula for returning sum of values for three months depending on the end date of a cell

Hi, I have a pretty difficult situation that I need help solving.

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.

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.

Any ideas are how I can get this started?

Very appreciated.

Note: I will be unable to share my files. I suppose if this is absolutely necessary, I could create a very small sample to give a visual of what was already put in words. I do not believe this should be necessary, however.

Originally Posted by amconnel

Note: I will be unable to share my files. I suppose if this is absolutely necessary, I could create a very small sample to give a visual of what was already put in words. I do not believe this should be necessary, however.

I think this is quite necessary...

Please see attached. I have edited the file so that fiscal year and quarter are now in the same column.

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.
Please think of this example to further understand the kind of formula I am needing:

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.
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
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.

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.
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.

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.
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.

Hi,
can you explain why would D8 in "home" tab is considered Q3 if the date id 1/10 ?

It may not be - would need to check with some higher ups. But this ending date would account for 9/2/2020 through 10/1/2020 and we do want that September data. The 'Home' Calendar Ending Date is just formatted differently than Competitor ending dates.

OK, so I would suggest you create a helper table like I did in 'competitor' sheet to define the quarters starting and ending dates.

Then use this in G26:
=SUMPRODUCT((Home!$D$2:$D$25>=VLOOKUP(C26,$L$2:$N$4,2,0))*((B26=Home!$B$2:$B$25))*(Home!$D$2:$D$25<=VLOOKUP(C26,$L$2:$N$4,3,0))*(Home!$F$2:$F$25))


Let me know if this solves your problem...

This almost works. It may be an error on my end, but when I use this in the original file and check, the numbers are not summed quite to where they need to be. I made some adjustments and believe I found the correct way, but is there a way to do this power query within excel? Don't have much experience with Power Query here.