• 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

salim hasan

New 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

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 :p
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!
 

Justmeok

New Member
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

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

Top