Dynamic criteria for SUMIF or dynamic getpivotdata

Mikes_KM

New Member
Joined
Jun 16, 2019
Messages
16
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 16, 2019
Messages
16
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

New Member
Joined
Jun 16, 2019
Messages
16
Thanks for updating me. I did not know. It has been posted elsewhere , I have provided the updates on both sites
 

Mikes_KM

New Member
Joined
Jun 16, 2019
Messages
16
This has been solved in another forum using =SUMIF([Trade week (Rank)],ROWS(AR$13:AR13),$T$13:T359)

:) . thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,455
Messages
5,487,002
Members
407,575
Latest member
calc

This Week's Hot Topics

Top