Dynamic criteria for SUMIF or dynamic getpivotdata

Mikes_KM

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

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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
 
Upvote 0
Thanks for updating me. I did not know. It has been posted elsewhere , I have provided the updates on both sites
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top