Using Sumifs & EDate to retrieve business model info

Hello all,

I want to begin by saying this will likely be difficult for me to explain, but I would assume the formula would be rather simple.

Let me try to explain


The objective: To be able to compare a businesses performance, separated out by state performances, on a standardized, "timing-neutral' basis. I will explain this below


I have a business modeled out for 8 calendar years. The 8 years are broken into months (columns).

This business is expanding into many different states, entering at different points in time during the 8 years.

I wan't to be able to compare states based on Year 1 of when each state was entered. For instance, the California market is entered in April of Calendar year 12. The Virginia market was first entered in December of 2012. I need to compare each of these market's performances on a standardized "year one, year two, etc..." basis. Unfortunately I built out all of the state revenue and expense numbers on one single Calendar Tab for the 8 years.

I can't just sum up January through December in every year of the 8, since none of the markets are entered at the same exact time and thus won't provide a "standardized" view of performance by state. I need to see how California performs in Year 1 vs. how Virginia performs in year one

To make it easier, I figure in each of the 8 columns (Calendar Years) that sum up the prior 12 months (to the left of each year) to equal the Calendar year total (2012, 2013, 2014, etc....), I will change that formula to effectively equal the following:

the sum of 12 months of revenue, whenever it may fall during the calendar years. Then, keep that logic consistent when extrapolated to 7 further 12 month periods over the 8 calendar years

to do this, I was thinking of changing that total column to sum up only the months with revenue, then it must count those months and decide the number of further months to be summed in the following Calendar year so that "# of summed months = 12". Then I need to have the formula follow this logic for the next year, effectively counting month #1 as the month that follows the last month summed up for that previous sum of 12 months.


I have provided an illustration in the attachment

*** I now am asking for your assistance to create a much more dynamic formula for the Year 1, Year 2 that can easily be inserted through out the model



I really appreciate the help guys and gals!

Hi, welcome to the forum

Thanks for the title change

Can you upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

(you probably need to look at using something like sumifS() with TODAY() and EDATE(TODAY(),-12)

Hi
Would something like this work, for example California Year 1?

Please Login or Register  to view this content.

Then repeat for other States and years.

DBY

Hi
Yes, I see, I hadn't thought it through correctly. If the sample file is a true representation of your data layout, would you consider using a few helper cells with the date calculations? See the attached reply file as an example. Maybe this might work, it uses the SUMIFS AND EDATE function suggested by F Dibbins earlier. I had to amend the dates across the top of the sheet as these were not all entered with the correct years.

In answer to the INDEX question. Although it will return a single value, it can also be used to return an array or range of values to a formula or function. It is very useful as replacement of Array formulas.

It seems that this might also work, without the need for helper cells:

Please Login or Register  to view this content.

This is placed in cell N7 and dragged across to AA7, delete formulas in between.

Hope this helps.
DBY