Log In
Register
Home
Active Member
May 7, 2019
Offline
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
Offline
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
Offline
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
Offline
If you actually have blanks in AN (which your current formula won't actually pick up) that's a little trickier. Do you have blanks? What kind of data should be in column AN - text or numbers (or both)?
Active Member
May 7, 2019
Offline
Hi Velouria,
Column AN has various text information and loads of blanks.
So I am basically trying to match with D33 if there is something in D33 but then if there is nothing in D33, just omit the filter completely (i.e. just return everything).
Thanks,
Alex
Trusted Members
Moderators
November 1, 2018
Offline
I think you'd need to use something like this as the criterion:
IF('Buy and Sell Criteria'!$D$33="",{"*","="},'Buy and Sell Criteria'!$D$33)
and then wrap the whole COUNTIFS formula in a SUM() function.
1 Guest(s)
