Multiple IF(AND

[owen4512]

Hi all,

I currently have a table with a with multiple drop down lists with the option "yes" or "No". I'm looking to find out everytime "yes" is selected 25 will be added to cell M6(where the formula currently is). I'm looking to do this so i can use a data bar to measure progress.

I've tried using IF(AND but cant seems to make it work.
Here's what i've tried so far;

=IF(AND([@[Test1]]="Yes",OR([@[Test2]]="",[@[Test2]]="Yes")),25,0) - This formula works just fine and does the trick but i'm struggling on how i would add more to this.

So i essentially want it so when test1/2 is "yes" to return 25(the formula above) and if test3 is "yes" then return 50. if test4 = "Yes" then return 75 and if test5 = "Yes" then return 100 - but i want all of this in one formula

I hope that makes sense

I appreciate any help on this.

 

[jimrward]

Are the cells contiguous for instance A2:E2, also does test1=yes and test2=yes count as 25 or 50

If 25 then =(countif(a2:e2,”yes”)-1)*25

If 50 then =countif(a2:e2,”yes”) * 25

 

[owen4512]

Are the cells contiguous for instance A2:E2, also does test1=yes and test2=yes count as 25 or 50

If 25 then =(countif(a2:e2,”yes”)-1)*25

If 50 then =countif(a2:e2,”yes”) * 25

Hi Jim,

Thanks for your reply.

Yes the cell range is from G6:K6. And yes if, test1 = yes and test 2 = yes then i only want 25 to be added

So the 25/50/75/100 will all be in the same cell so i'm looking for the formula only i one cell if possible

 

[jimrward]

Something like =choose((countif(g6:k6,”yes”)-1),”25”,”25/50”,”25/50/75”,”25/50/75/100”)

Not on my pc at the moment so what happens if test1=yes, test2=no and test3=yes

 

[owen4512]

Something like =choose((countif(g6:k6,”yes”)-1),”25”,”25/50”,”25/50/75”,”25/50/75/100”)

Not on my pc at the moment so what happens if test1=yes, test2=no and test3=yes

This doesnt seem to work. im getting an error on the part highlighted in red;

=choose((countif(g6:k6,”yes”)-1),”25”,”25/50”,”25/50/75”,”25/50/75/100”)

 

[owen4512]

This doesnt seem to work. im getting an error on the part highlighted in red;

=choose((countif(g6:k6,”yes”)-1),”25”,”25/50”,”25/50/75”,”25/50/75/100”)

Apologies i think i misunderstood. I have tried the below but still getting an error;

=choose((countif(g6:k6,”yes”)-1),”25”,”50”,”75”,"100”)

 

[Joe4]

Note: That Excel and VBA are VERY particular and do NOT like slanted quote marks.
So you will need to replace them with the straight quote marks, i.e.

[COLOR=#333333]=choose((countif(g6:k6,"yes")-1),"25","50","75","100")[/COLOR]

 

[jimrward]

ok change the double quotes to ones on your keyboard ipad ones are different

 

[Ryan1996]

Based on something i did previously this could work but not sure due the the tests as i used cells instead. You can add more IF's following the path below.

=IF([@[Test1]]="Yes","25",IF([@[Test2]]="Yes","25",IF([@[Test3]]="Yes","50",IF([@[Test4]]="Yes","75",IF([@[Test5]]="Yes","100","0")))))

 

[owen4512]

Jims solution seems to have worked. Thanks everyone for your help!!