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

Running total

M

Manuel998

Member
Hi

I am looking for some assistance pls. I am trying to get the cumulative total for 12 months every time i select the date in Sheet 1 cell D1 i.e. e.g. if i select 2019-02 it should pick all the data with 2018 03 to 2019 02 tags therefore always picking 12 months running total. I have tried to use a sumif but its not dynamic as i have to change the criteria manually in the formulae. your assistance will be much appreciated workbook attached
 

Attachments

  • Test.xlsx
    11 KB · Views: 7
Another method.
=SUMPRODUCT((DATEVALUE(Data!C4:C17&"-1")>=EDATE(DATEVALUE(D1&"-1"),-11))*(DATEVALUE(Data!C4:C17&"-1")<=DATEVALUE(D1&"-1")),Data!L4:L17)
 
Thanks team - but is there a way i can incorporate cells b4,c4,d4,e4,f4 & g4 in the criteria as the data set needs to be summed for that criteria only?
 
Manuel998
Your sample result was 102.
Did You explain something else there?
Now, You would like to do something else.
Could You show Your new wanted results with that data?
 
Hi - sheet attached the formulae in e7 works and if that can be tweaked for the criteria in the data sheet filtered or in B4-F4 (sheet 1) that would be much appreciated. pls note the data is only a snapshot there is over 350k lines of data and the dates are more than what i have attached but if i can get the principle i can replicate this in my workbook. thanks in advance
 

Attachments

  • Test.xlsx
    22.2 KB · Views: 7
What version of Excel do you have?

If PQ/Get & Transform isn't an option there are other methods available.

1. MS Query - This will be bit cumbersome and you may find it bit difficult to maintain / change query string.
2. Advanced Filter (in place), then use Subtotal() - This is easier to maintain, but will require different / additional set up for criteria range.

If using PQ, you can pass criteria as variables (using named ranges) to PQ to filter data set. Then data is loaded to data model. DAX measure is used to calculate result. CUBE function is used on the sheet to retrieve result of DAX measure.
 
i have 2016 and PQ but the individuals i am sending this report to to dont have PQ and are not trained to use PQ. any chance the formulae you had @Chihiro can be tweaked to include the criteria i gave in sheet 1 pls?
 
Personally, I'd just use small VBA code and Subtotal. Rather than formula...

Also your manual result in C7 looks to be wrong. Should it not be only 1 record that matches criteria?

Assuming above.
=SUMPRODUCT((DATEVALUE(Data!C4:C173&"-1")>=EDATE(DATEVALUE(D1&"-1"),-11))*(DATEVALUE(Data!C4:C173&"-1")<=DATEVALUE(D1&"-1"))*(Data!D4:D173=B4)*(Data!E4:E173=C4)*(Data!F4:F173=D4)*(Data!G4:G173=E4)*(Data!H4:H173=F4),Data!L4:L173)
 
You must log in or register to reply here.
Back
Top