I have problems with conditions

[Greenlee]

I think its quite complicated so I would need to show you but i will try

So this is my table (sheet name: BACKTESTING)
Gyazo - 18e6618f6f26ba50b4937012cb11de5a.png

This is my second sheet (sheet name BACKTESTING RESULTS)

Gyazo - 8a07ad8210ee284c294add4d27a7adb5.png

So what do I want to do:

so this is the formula for now:
=COUNTIFS(BACKTESTING!D3:D7;"=BAT";BACKTESTING!B3:B7;"=EUR/AUD";BACKTESTING!C3:C7;"=M15";BACKTESTING!K3:K7;"=1") from backtesting page but I want to add 2 more conditions from 2nd sheet
1st when you choose TF=timeframe M15 that would show results for M15 not for M5,H1 or H4 and second when I would choose on the right side EUR/AUD it would show EUR/AUD not also EUR/JPY ( dropdown of currencies) ...

If I was not understandable enough please let me know

 

[Chris Mack]

=COUNTIFS(BACKTESTING!D3:D10000;B2;BACKTESTING!B3:B10000;F2;BACKTESTING!C3:C10000;E3;BACKTESTING!K3:K10000;"=1")

Hope this helps,

Chris.

 

[Greenlee]

=COUNTIFS(BACKTESTING!D3:D10000;B2;BACKTESTING!B3:B10000;F2;BACKTESTING!C3:C10000;E3;BACKTESTING!K3:K10000;"=1")

Hope this helps,

Chris.

Nope this code its not good because this is the condition: It should be like this...if this condition is true

COUNTIFS(BACKTESTING!D3:D10000;B2;BACKTESTING!B3:B10000;F2;BACKTESTING!C3:C10000;E3;BACKTESTING!K3:K10000;"=1"

then it needs to calculate from BACKTESTING!N3:N10000 BACKTESTING!P3:P10000

I hope i was more understandable this time :D

 

[Chris Mack]

Sorry I don't get it.

Your condition is identical to my formula?

 

[Greenlee]

Okay so let me try to explain one more time what exactly I need now

Here is the picture: Gyazo - 075dea57983a6059266ff76a220ffd3d.png next to AVG pips [ Where it says here] I would like to have those conditions

COUNTIFS(BACKTESTING!D3:D10000;B2;BACKTESTING!B3:B10000;F2;BACKTESTING!C3:C10000;E3;BACKTESTING!K3:K10000;"=1"</pre>

and if those conditions are correct then I want that it sums (calculates) Gyazo - 8c89f2b11e96bb454fbef3691727eb50.png from backtesting sheet from N3:N1000 + P3:N1000 in particular case ( 172 ) and divides by number of K3:K10000;"=1" i hope i was clear its hard to explain heeh :D

 

[Chris Mack]

=SUM(IF(BACKTESTING!D3:D10000=B2;IF(BACKTESTING!B3:B10000=F2,IF(BACKTESTING!C3:C10000=E3,IF(BACKTESTING!K3:K10000=1,(BACKTESTING!N3:N10000+BACKTESTING!P3:P10000))))))/SUM(IF(BACKTESTING!D3:D10000=B2;IF(BACKTESTING!B3:B10000=F2,IF(BACKTESTING!C3:C10000=E3,IF(BACKTESTING!K3:K10000=1,BACKTESTING!K3:K10000)))))

Entered with CTRL+SHIFT+ENTER.

How's that?

Chris.

 

[Greenlee]

=SUM(IF(BACKTESTING!D3:D10000=B2;IF(BACKTESTING!B3:B10000=F2,IF(BACKTESTING!C3:C10000=E3,IF(BACKTESTING!K3:K10000=1,(BACKTESTING!N3:N10000+BACKTESTING!P3:P10000))))))/SUM(IF(BACKTESTING!D3:D10000=B2;IF(BACKTESTING!B3:B10000=F2,IF(BACKTESTING!C3:C10000=E3,IF(BACKTESTING!K3:K10000=1,BACKTESTING!K3:K10000)))))

Entered with CTRL+SHIFT+ENTER.

How's that?

Chris.

with this code Gyazo - 263fcd96ad627b1206e4f6ffdf14064d.png i get an error Gyazo - 510193c5bb94bb5d89dbb358f88e8491.png don't know what is wrong

 

[Chris Mack]

Ok, firstly make sure you're holding down CTRL+SHIFT when you press enter to enter the formula.

Secondly, I'd try replacing the semi-colons with commas as it could be a syntax thing.

Next, separate the two SUM(IFs, i.e. try each one in the cell separately and see which is causing the error, possibly running 'Evaluate Formula' and seeing where specifically the error is coming from.

Chris.