Active Member
May 7, 2019
Hi all,
I have the following formula which is working but I just added the AN D33 Criteria and I want it to return the values if it is a match in that column. But if the cell D33 is blank, it needs to eliminate the criteria. i.e. just return everything. At the moment, as it stands, obviously if D33 is blank, it only returns blanks in AN.
[FORMULA]=IFERROR(IF(COUNTIFS('IA Asia Pacific Ex Japan RAW'!$AK$2:$AK$300,"<="&'Buy and Sell Criteria'!$E$13,'IA Asia Pacific Ex Japan RAW'!$AP$2:$AP$300,"="&'Buy and Sell Criteria'!$C$32,'IA Asia Pacific Ex Japan RAW'!$AA$2:$AA$300,"<="&'Buy and Sell Criteria'!$C$11,'IA Asia Pacific Ex Japan RAW'!$Y$2:$Y$300,"<="&'Buy and Sell Criteria'!$C$12,'IA Asia Pacific Ex Japan RAW'!$O$2:$O$300,">="&'Buy and Sell Criteria'!$C$4,'IA Asia Pacific Ex Japan RAW'!$S$2:$S$300,"<="&'Buy and Sell Criteria'!$C$5,'IA Asia Pacific Ex Japan RAW'!$AB$2:$AB$300,">="&'Buy and Sell Criteria'!$C$6,'IA Asia Pacific Ex Japan RAW'!$AO$2:$AO$300,"<="&'Buy and Sell Criteria'!$E$8,'IA Asia Pacific Ex Japan RAW'!$E$2:$E$300,"<="&'Buy and Sell Criteria'!$C$3,'IA Asia Pacific Ex Japan RAW'!$AN$2:$AN$300,'Buy and Sell Criteria'!$D$33)<ROWS($A$2:A2),””,INDEX('IA Asia Pacific Ex Japan RAW'!$A$2:$A$300,SMALL(IF(('IA Asia Pacific Ex Japan RAW'!$AK$2:$AK$300<='Buy and Sell Criteria'!$E$13)+('IA Asia Pacific Ex Japan RAW'!$AP$2:$AP$300='Buy and Sell Criteria'!$C$32)+('IA Asia Pacific Ex Japan RAW'!$AA$2:$AA$300<='Buy and Sell Criteria'!$C$11)+('IA Asia Pacific Ex Japan RAW'!$Y$2:$Y$300<='Buy and Sell Criteria'!$C$12)+('IA Asia Pacific Ex Japan RAW'!$O$2:$O$300>='Buy and Sell Criteria'!$C$4)+('IA Asia Pacific Ex Japan RAW'!$S$2:$S$300<='Buy and Sell Criteria'!$C$5)+('IA Asia Pacific Ex Japan RAW'!$AB$2:$AB$300>='Buy and Sell Criteria'!$C$6)+('IA Asia Pacific Ex Japan RAW'!$AO$2:$AO$300<='Buy and Sell Criteria'!$E$8)+('IA Asia Pacific Ex Japan RAW'!$E$2:$E$300<='Buy and Sell Criteria'!$C$3)+('IA Asia Pacific Ex Japan RAW'!$AN$2:$AN$300='Buy and Sell Criteria'!$D$33)=10,ROW('IA Asia Pacific Ex Japan RAW'!$A$2:$A$300)-ROW('IA Asia Pacific Ex Japan RAW'!$A$2)+1),ROWS($A$2:A2)))),"")[/FORMULA]
Any help would be much appreciated!!
Many thanks in advance,
Alex
Trusted Members
Moderators
November 1, 2018
It looks like you could use:
+(IF('Buy and Sell Criteria'!$D$33="",1,'IA Asia Pacific Ex Japan RAW'!$AN$2:$AN$300='Buy and Sell Criteria'!$D$33))
Also note that you can multiply the criteria instead of adding and comparing to 10 since it looks like you want them all to be true.
Active Member
May 7, 2019
Amazing, thanks Velouria. I have replaced the second part of the formula with that bit but what about the first bit of the formula, i.e. the
'IA Asia Pacific Ex Japan RAW'!$AN$2:$AN$300,'Buy and Sell Criteria'!$D$33
For the COUNTIFS criteria and array?
Because just replacing the second half other bit with your bit doesnt seem to work?
Thanks once again for looking at this!
Alex
Trusted Members
Moderators
November 1, 2018
1 Guest(s)