# Dynamic criteria for SUMIF or dynamic getpivotdata

#### Mikes_KM

##### New Member
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

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.

#### sheetspread

##### Well-known Member
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.

#### Fluff

##### MrExcel MVP, Moderator
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
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
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
This has been solved in another forum using =SUMIF([Trade week (Rank)],ROWS(AR\$13:AR13),\$T\$13:T359)

. thanks!

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