IF Function with Multiple Criteria

I have following formula wherein first part of the formula excludes 14 and 51 from the calculation, now i want to add in exclusion 61 as well. Any solution for the flexible formula where i can keep on adding any number to exclusion. kindly assist


=IF(AND(I3<>14,I3<>51),IF(AND(K3>=$KP$1,K3<=$KQ$1) ,(U3*20653750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$3262,0),0))


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9c-vOQApTgb
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9c-vbihZ-7W
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9c-vfmpSO0F
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9c-vjfTJ7lX
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9c-vmq-LHHz
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9c-vst3j_7i
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwBqjIR5Nj
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwBw8El0r5
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwC63GbRuM
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwC9fyKZdo
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bwCEn8DBQe
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg. 9bbxud383FI9bw0Bey8gQO
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

HI,

Try

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*20653750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$3262,0),0))

You could replace the array (in red ) with a range as well.

Great, its working. if i want other way arround i have to remove ISNA, is it?
Thanks

Hi,

No, ISNA will still be there. Just replace those array of values with a range.

Quote:

---

Originally Posted by Admin

HI,

Try

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*20653750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$3262,0),0))

You could replace the array (in red ) with a range as well.

---

In the above formula if I want to add another range match(N3,{CA,CN,KR} without changing the first one , how would I do that?

Kindly assist.

Hi

Not sure whether you want to match or not to match.

1. to match N3 with the array

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(MATCH(N3,{"CA" ,"CN","KR"},0),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*206 53750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$3262,0),0)))

2. This will exclude the match

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(ISNA(MATCH(N3, {"CA","CN","KR"},0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),( U3*20653750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$3262,0),0)))

Thanks, however, the second formula gives me FALSE for values excluded in first match i.e 14,51,61. And first formula gives me N/A#. Can this be fixed with and / or, as i want both match excluded from the calculation. e.g if it finds 14,51,61 and also ca,cn,kr the formula should return 0.

I hope the above is clear.

Hi

Does this

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(ISNA(MATCH(N3, {"CA","CN","KR"},0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),( U3*20653750)/$D$3257,IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*1 50030)/$D$3262,0),0))),0)

correct result ?

not working bracket error, my original formula is as below, it does not move,

=IF(ISNA(MATCH(I3,PC_CC,0)),IF(ISNA(MATCH(R3,Risk_ country,0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*206537 50)/$D$4772,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$4779,0),0))),0) where pc_cc includes 14,51,33 etc and risk_country includes CN, RU, etc.

and if i change to

=IF(ISNA(MATCH(I3,PC_CC,0)),IF(ISNA(MATCH(R3,Risk_ country,0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*206537 50)/$D$4772,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$4779,0),0)),0)

it does not perform the IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*150030)/$D$4779,0),0)),0) returns 0.

=IF(ISNA(MATCH(I3,PC_CC,0)),IF(ISNA(MATCH(R3,Risk_ country,0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*206537 50)/$D$4772,IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*1 50030)/$D$4779),0)),0),0)

No still it does not work for last formula where it is equal to 14 and it is excluded in first match, returns 0.

Hi

Can you attach a sample workbook with some dummy values along with expected results ?

sample is attached and hope i have made it clear.

Hi

I think this is the formula you want.

=IF(AND(ISNA(MATCH(A3,pc_cc,0)),ISNA(MATCH(C3,coun ,0))),IF(AND(A3<>14,B3>=$E$1,B3<=$F$1),(D3*2065375 0)/172884227,IF(AND(A3=14,B3>=$E$1,B3<=$F$1),(D3*1500 30)/6083342,0)),0)

and if you evaluate this formula

first it checks

AND(ISNA(MATCH(A3,pc_cc,0)),ISNA(MATCH(C3,coun,0)) )

if it evaluates TRUE, the next step

IF(AND(A3<>14,B3>=$E$1,B3<=$F$1)

.. and if it evaluates TRUE, then it calculates (D3*20653750)/172884227

or if it evaluates FALSE, then evaluates this one.. IF(AND(A3=14,B3>=$E$1,B3<=$F$1)

If it TRUE, (D3*150030)/6083342, otherwise 0

Is it what you are after ?

if not, give me the cell values for E3:E18.

I have attached sample data with required values;

Kindly see.

Hi,

In E7, this ISNA(MATCH(A7,pc_cc,0)) returns FALSE.

Ok, any way out to get correct values. But this works fine if I dont put the second match which excludes coun.