# Value Error - if and statement

[jgold20]

I have a if and statement that I am receiving a value error on. If I just use 1 if statement it works properly. When I add a second if statement, I receive a #Value error




=IF(AND(C3="B",I3="YES"),"Min. Amt / 286","") +
IF(AND(C3="W",I3="YES"),"Min. Amt / 286","")

 

[Aladin Akyurek]

It seems my assumptive stipulations do not hold.

We need mutually exclusive if-then rules. Otherwise, it is hard to cover all possibilities.

 

[jtakw]

It would have been nice if you had explained in Detail what you are looking to accomplish at the beginning, so we don't have to guess and spend time on asking questions back and forth and spend time on coming up with solutions that's Not relevant to what You have in mind.

Based on the previous 3+ pages of Q & A, there would be 7 possible combination of outcomes (8, if we include "Blank" when I3 is not "Yes"), this seems to be the solution for your question.

Book1
	C	D	E	F	G	H	I
2	Company Code	Special Instructions		Enter Min. Move Amt.	Min.Price per CF	Filing OR Scheduling & CC Fees	Minimum Move
3	B	to West: NM,AZ,CA,ND,OR, WA,SD,WA,ID,WY,CO,NE		$1,500.00	$5.24	NO	YES
4	L			$1,500.00	$ -		Min. Amt / 200 / 286 / 300
5	N			$1,500.00	$ -
6	W			$1,500.00	$5.24
7	MD				$ -
Sheet58

Cell Formulas
Range	Formula
I4	=IF(I3="Yes","Min. Amt / "&CHOOSE(SUM(IF(SUM(COUNTIF(C3:C7,{"B","W"}))>=1,1,0),IF(SUM(COUNTIF(C3:C7,{"MD","MN"}))>=1,2,0),IF(SUM(COUNTIF(C3:C7,{"<>B","<>W","<>MD","<>MN"}))>=16,4,0)),286,300,"286 / 300",200,"200 / 286","286 / 300","200 / 286 / 300"),"")

I've read your previous post here: https://www.mrexcel.com/forum/excel-questions/1045070-if-statement-problem.html#post5016745

So I hope you have "cleaned-up" the values in your drop down list; otherwise, my (or anyone's) formula will fail.
Also, I see that you keep using the plus sign ( + ) to combine IF statements, you might want to google "Excel IF function" and "Excel Nested IF" to get a better understanding of how to write them.

 

[markmzz]

C3:C7 will always have an ALPHA value

C3=B, C5=H, all other cells are empty >> / 286, / 300, / 200

C3=B, C4=H, C5=Q, C6=R, C7=X >> / 286 /200

C3=B, C4=MN, all other cells are empty >> / 286, / 300, / 200

C3=MD, C4=H, C5=Q, C6=R, C7=X >> / 300 /200

C3=B, C4=B, all other cells are empty >> / 286

C3=MD, C4=H, C5=B, C6=H, C7=R >> / 300 / 286 /200

C7=L, all other cells are empty >> / 286, / 300, / 200

C7=L, all other cells are empty >> / 286, / 300, / 200

C3=D, C4=H, C5=V, C6=H, C7=R >> /200

Hi!

I think you should explain the rules to the desired results of the posts #29 and #30.

Markmzz