Dynamic criteria for SUMIF or dynamic getpivotdata

[Mikes_KM]

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/10oSxeUdJeIqhrxaAchfWf5MXq4NBTQ4T/view?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

 

[sheetspread]

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.

 

[Mikes_KM]

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/1i3CrnXk2PCXw6INzqSvUSXPvS1ff5nSd/view?usp=sharing

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...iteria-for-sumif-or-dynamic-getpivotdata.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.

 

[Mikes_KM]

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

 

[Mikes_KM]

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

 

[Mikes_KM]

Here is the link to the other forum - https://www.excelforum.com/excel-fo...umif-or-dynamic-getpivotdata.html#post5160014

 

[Mikes_KM]

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

. thanks!