• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • Rolling Period Totals.xlsx
    12.3 KB · Views: 8
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),)))
 
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 :)
 
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

  • Copy of Rolling Period Totals.xlsx
    13.7 KB · Views: 11
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 :p
Thank you so so much again for your help!
 
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))
 
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!
 
Right well I'm not too sure why but I couldn't get either of the above to work properly :rolleyes: 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

  • Copy of Rolling Period Totals with formula.xlsx
    28.6 KB · Views: 5
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

  • Rolling Period Totals with formula_2.xlsx
    39.6 KB · Views: 7
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 :p No pressure of course if you never get back to it - your help has been invaluable to me :DD:DD My sincere thanks to you!
 
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

  • Copy of Rolling Period Totals with formula_2.xlsx
    42.1 KB · Views: 1
Back
Top