# Formula to Sum rolling periods with multiple criteria from two non contiguous ranges possible?

#### Justmeok

##### New Member
Hi everyone
I have a colleague's spreadsheet that I cannot change the structure of but I need a formula to handle a sum function. The formula needs to update automatically rather than having to be adjusted manually each time.
I am looking for a formula that will sum a rolling 12 month period based on the individual store names (in rows) and monthly columns BUT also in separate ranges. I have added a helper row with a formula in the attached example as I was trying to write a SUMIFS formula but have discovered that won't work as the ranges are not equal. (although I could be totally incorrect here as well!!) I am really out of my depth here so would appreciate any help I can get. Please note that the solution must be a formula as it's part of a much larger report.
I have attached a simple example of what I am looking for

Thanks everyone

#### Attachments

• 12.3 KB Views: 8

#### salim hasan

##### Member
Try this formula in the cell S6 and drag down
Code:
`` =IF(COUNTIF(\$A\$4:\$A\$13,\$R6)=0,"",SUM(INDEX(\$C\$4:\$N\$13,MATCH(\$R6,\$A\$4:\$A\$13,0),)))``

#### Justmeok

##### New Member
Thank you Salim but I haven't explained it too well. Your formula works on the top table but it is including Columns C & D in the first table instead of excluding them and including Columns C & D from the second table - in other words I need to count back 12 months from the date in cell P1. In this case that would give me the spread of dates as Sep 18 to Aug 19. Thank you very much for your suggestion though I do appreciate it

#### GraH - Guido

##### Well-Known Member
Hi,

If you don't mind using a helper column in column B, try this:
- Add in name manager the formula = EOMONTH(DATE,-12) and name it RefMth
- [B4] = SUMPRODUCT(\$C4:\$CCC4*(\$C\$3:\$CCC\$3>vRefMth)) and drag this down to cell B13
- [B18] =SUMPRODUCT(\$C18:\$CCC18*(\$C\$17:\$CCC\$17>vRefMth)), and drag down to cell B29

In your table on another sheet
=SUMIF(Data!\$A:\$A,Result!\$B5,Data!\$B:\$B)

#### Attachments

• 13.7 KB Views: 11

#### Justmeok

##### New Member
GraH - Guido you are my hero!!! This is awesome and works perfectly Thank you so much for attaching the example spreadsheet this was really very easy to follow. Now may I push the boundaries of friendship a little more and ask you for more help please? I neglected to mention in my original post that I would be using this formula to compare this year vs last year for the same periods. What I did was create a second helper column for the last year data but because the year has a full spread of dates they are being included which of course doesn't happen with the current year as future date periods are blank. I created a second named range for both Date & RefMth but I think I need to have it so that there is another criteria so that it doesn't return results that are greater than the date. In other words a range of dates no more than 12 months prior and no future dates. Hope this is making sense to you! In the meantime I will try to tweak this myself and see if I can come up with a solution on my own - doubtful but I'm willing to try
Thank you so so much again for your help!

#### GraH - Guido

##### Well-Known Member
Hi, I learned something myself and it turns out that the sumproduct formulae can be turned into sumif(s) on the horizontal range as well. Have to think hard if I ever did that before, I normally organise my data column wise, meaning dates are in 1 column.

You might want to try making 2 more reference dates:
- RefMthYr0 as EDATE(RefMth,-12)
- RefDteYr0 as EOMONTH ( TODAY ( ), -12 ) +1
Then add an extra argument in SUMPRODUCT(\$C4:\$CCC4*(\$C\$3:\$CCC\$3<=vRefDteYr0)*(\$C\$3:\$CCC\$3>vRefMthYr0))

#### Justmeok

##### New Member
Ahh thank you again for your reply I think I understand so I'll give it a try and get back to you
I appreciate your help so much!

#### GraH - Guido

##### Well-Known Member
Actually, RefMthYr0 as EDATE(RefMth,-12) would do, no? I always struggle if I don't see the data...

#### Justmeok

##### New Member
Right well I'm not too sure why but I couldn't get either of the above to work properly I fiddled around for a bit with different variations and finally googled the EOM function and then I came up with the attached solution. It does appear to be working correctly so I am hopeful my ignorance of this function won't trip me up in the future! Would you be so kind as to cast your eye over my solution and let me know your thoughts please? (Like you I always have the dates in one column too so this layout is not something I would normally deal with either!)
Thanks again for your help, very much appreciated

#### Attachments

• 28.6 KB Views: 5

#### GraH - Guido

##### Well-Known Member
Hi, justmeok,
If it were my workbook, I probably would just leave the "raw data" for what it is, and make a new workbook to summarize per calendar years that would be using powerquery to unpivot the data and group the data per year/store. I made a start version that still needs some tweaking on rolling months approach. But just to give you an idea of what you could do.

For readability I've changed the name references (using 1 name less) and turned to SUMIFS.

#### Attachments

• 39.6 KB Views: 7

#### Justmeok

##### New Member
Hi GraH - Guido
Sorry I haven't got back to you recently but it's been crazy busy!

Yes I agree if this was my workbook I would have it structured completely differently and used queries and pivots to obtain the data. In this case though it is part of a much larger workbook structure and the owner wants to leave it as it is. It would be a time consuming task to completely re-structure but way more efficient however each to their own.......

I would be very interested to see the end result of the power query tweak for the rolling periods as I can already see a few other applications that could benefit from this approach I might have a crack at it myself although I would not classify my power query skills as anything but basic so I don't hold a lot of hope for a successful outcome No pressure of course if you never get back to it - your help has been invaluable to me My sincere thanks to you!

#### GraH - Guido

##### Well-Known Member
Something like this is doable with Power Query.
I've not optimized/not documented the code, but if you through each of the applied steps it should be straightforward to replicate.
If not, help is not far way.

#### Attachments

• 42.1 KB Views: 1