Multiple IF(AND

owen4512

Board Regular
Joined
Dec 10, 2014
Messages
71
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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”)
 
Upvote 0
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”)
 
Upvote 0
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.
Code:
[COLOR=#333333]=choose((countif(g6:k6,"yes")-1),"25","50","75","100")[/COLOR]
 
Upvote 0
ok change the double quotes to ones on your keyboard ipad ones are different
 
Upvote 0
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")))))
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top