Dynamic criteria for SUMIF or dynamic getpivotdata
Results 1 to 8 of 8

Thread: Dynamic criteria for SUMIF or dynamic getpivotdata

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Dynamic criteria for SUMIF or dynamic getpivotdata

    Hi Everyone,

    I need help with a spreasheet issue related to my trading strategy.

    It would be great if I could upload the excel file, but it is too large. So I will carefully explain the problem with several images labelled in a word document - https://drive.google.com/file/d/10oS...ew?usp=sharing


    Image 1 Shows - The week# and closed pips (for the specified week #) [as shown by 83.6, 25.3, 32.3 etc]

    If we zoom into the formula for closed pips, we see that it contains a complex getpivotdata formula (Image 2)
    The pivot table is hidden. If I unhide the pivot table, we get image 3

    We can zoom into where the data comes from as shown in images 4 and 5.
    Image 4 shows - Trade week (rank)
    Image 5 shows - Total PnL


    My problem:
    I need the "Closed Pips" as shown in image 1 to be dynamic. For example, if I filter out some data in my spreadsheet, the closed pips returned in a week should change. But it does not. At the moment, I would have to manually filter the data in the hidden pivot table.

    What I have done so far:
    I have created a SUMIF formula as shown in image 6. This formula needs to be improved on. Ideally, the criteria "=1" should be dymamic. As it represents the week.

    If we look at image 7 - you can see we get the same -83.6 as we get at the top of closed pips in image 1. However, as the criteria is not dynamic, we fail to get 25.3 below as a representation of the closed pips in week 2. Instead we get 78.3.


    What I'd like help with:
    1. Either fixing this formula, so I can replace the complex getpivotdata formula shown in image 1

    OR

    2. Some how make the complex getpivotdata dynamic so that as I filter things throughout my spreadsheet, the closed pips reflects the change.


    Thank you

  2. #2
    Board Regular
    Join Date
    Sep 2005
    Posts
    5,097
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic criteria for SUMIF or dynamic getpivotdata

    Quote Originally Posted by Mikes_KM View Post
    It would be great if I could upload the excel file, but it is too large.
    Are you able to scale it down at all? This does look like an interesting problem.

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic criteria for SUMIF or dynamic getpivotdata

    Hey thanks for your interest. I have created a google sheet link. It may be best to download the file and opening it with microsoft excel
    Link = https://drive.google.com/file/d/1i3C...ew?usp=sharing


  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,665
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Dynamic criteria for SUMIF or dynamic getpivotdata

    Cross posted https://www.excelforum.com/excel-for...pivotdata.html

    While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
    This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic criteria for SUMIF or dynamic getpivotdata

    I have come close. What I did was fix the beginning of the range and use this formula - =SUMIF([Trade week (Rank)],1,$T$13:T359)

    Now one issue remains: I would like to copy this formula down. The criteria "1" needs to increase in increments of 1 eg. =SUMIF([Trade week (Rank)],2,$T$13:T359) , SUMIF([Trade week (Rank)],3,$T$13:T359) etc


    Thanks

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic criteria for SUMIF or dynamic getpivotdata

    Thanks for updating me. I did not know. It has been posted elsewhere , I have provided the updates on both sites

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic criteria for SUMIF or dynamic getpivotdata

    Here is the link to the other forum - https://www.excelforum.com/excel-for...ml#post5160014

  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic criteria for SUMIF or dynamic getpivotdata

    This has been solved in another forum using =SUMIF([Trade week (Rank)],ROWS(AR$13:AR13),$T$13:T359)

    . thanks!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •