# 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","")

 

[jtakw]

Hi,

You didn't explain the logic behind your formula.
Since C3 can not be "B" and "W" at the same time, I'm assuming C3 can be either B or W, and I3 must be "Yes", then result "Min. Amt / 286", otherwise Blank:

Book1
	A
1
Sheet58

Cell Formulas
Range	Formula
A1	=IF(AND(OR(C3={"B","W"}),I3="YES"),"Min. Amt / 286","")

 

[jgold20]

The logic you assumed is correct. I gave only a partial example. With your formula, it works with 1 if statement. If I add a 2nd if statement, I still receive the #value error on the second if statement

=IF(AND(OR(C3={"B","W"}),I3="YES"),"Min. Amt / 286","") +
IF(AND(OR(C3={"MD","MN"}),I3="YES"),"Min. Amt / 300","")

 

[jtakw]

The logic you assumed is correct. I gave only a partial example. With your formula, it works with 1 if statement. If I add a 2nd if statement, I still receive the #value error on the second if statement

=IF(AND(OR(C3={"B","W"}),I3="YES"),"Min. Amt / 286","") +
IF(AND(OR(C3={"MD","MN"}),I3="YES"),"Min. Amt / 300","")

Above in red, might I ask why?
Above in green, no, we've already combined your original 2 IF statements.

Book1
	B
1
Sheet58

Cell Formulas
Range	Formula
B1	=IF(I3="YES",IF(OR(C3={"B","W"}),"Min. Amt / 286",IF(OR(C3={"MD","MN"}),"Min. Amt / 300","")),"")

 

[jgold20]

I forgot there was another condition to test

 

[jtakw]

No problem, that happens quite often.
Is it working for you now?

 

[jgold20]

I was just informed that there are additional cells to test 0- same error when I try and use the prior formula:

=IF(I3="YES",IF(OR(C3={"B","W"}),"Min. Amt / 286",IF(OR(C3={"MD","MN"}),"Min. Amt / 300","")),"") +
IF(I3="YES",IF(OR(C4={"B","W"}),"Min. Amt / 286",IF(OR(C4={"MD","MN"}),"Min. Amt / 300","")),"") +
IF(I3="YES",IF(OR(C5={"B","W"}),"Min. Amt / 286",IF(OR(C5={"MD","MN"}),"Min. Amt / 300","")),"") +
IF(I3="YES",IF(OR(C6={"B","W"}),"Min. Amt / 286",IF(OR(C6={"MD","MN"}),"Min. Amt / 300","")),"") +
IF(I3="YES",IF(OR(C7={"B","W"}),"Min. Amt / 286",IF(OR(C7={"MD","MN"}),"Min. Amt / 300","")),"")

 

[jtakw]

Ok, first, I'd like to point out, you can't use the plus sign ( + ) to combine IF conditions like that, the plus sign basically acts like an AND statement, so all those conditions can not be True at the same time

See if this works for you:

Book1
	C
1
Sheet58

Cell Formulas
Range	Formula
C1	=IF(I3="YES",IF(OR(SUM(COUNTIF(C3:C7,{"B","W"}))=5),"Min. Amt / 286",IF(OR(SUM(COUNTIF(C3:C7,{"MD","MN"}))=5),"Min. Amt / 300","")),"")

 

[jgold20]

Nothing displays now - below the yes, should display the message "Min. Amt / 286" as "B" is in c3:

=IF(I3="YES",IF(OR(SUM(COUNTIF(C3:C7,{"B","W"}))=5),"Min. Amt / 286",IF(OR(SUM(COUNTIF(C3:C7,{"MD","MN"}))=5),"Min. Amt / 300","")),"")

[TABLE="width: 675"]
<tbody>[TR]
[TD="class: xl76, width: 95, align: center"]B[/TD]
[TD="class: xl82, width: 183, colspan: 2, align: center"]to West: NM,AZ,CA,ND,OR, WA,SD,WA,ID,WY,CO,NE[/TD]
[TD="class: xl80, width: 105, align: center"] $ 1,500.00[/TD]
[TD="class: xl81, width: 95, align: center"] $ 5.24[/TD]
[TD="class: xl77, width: 104, align: center"]NO[/TD]
[TD="class: xl84, width: 93, align: center"]YES[/TD]
[/TR]
[TR]
[TD="class: xl78, align: center"]L[/TD]
[TD="class: xl83, width: 183, colspan: 2, align: center"][/TD]
[TD="class: xl80, width: 105, align: center"] $ 1,500.00[/TD]
[TD="class: xl79, align: center"] $ -[/TD]
[TD="class: xl73"][/TD]
[TD="class: xl85, width: 93, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl74, align: center"]N[/TD]
[TD="class: xl82, width: 183, colspan: 2, align: center"][/TD]
[TD="class: xl80, width: 105, align: center"] $ 1,500.00[/TD]
[TD="class: xl75, align: center"] $ -[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl72, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl74, align: center"]W[/TD]
[TD="class: xl82, width: 183, colspan: 2, align: center"][/TD]
[TD="class: xl80, width: 105, align: center"] $ 1,500.00[/TD]
[TD="class: xl75, align: center"] $ 5.24[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl72, align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

EDIT to my above post:

One more thought.

Again, you didn't explain the logic here.

If C3:C7 Has to have either a "B" or "W" in All 5 cells, for the "Min. Amt / 286" result, or
C3:C7 Has to have either "MD" or "MN" in All 5 cells, for the "Min. Amt / 300" result, use formula in C1.

If Only One of the cells C3:C7 need "B" or "W", OR "MD" or "MN", then use formula in D1.

Book1
	C	D
1
Sheet58

Cell Formulas
Range	Formula
C1	=IF(I3="YES",IF(OR(SUM(COUNTIF(C3:C7,{"B","W"}))=5),"Min. Amt / 286",IF(OR(SUM(COUNTIF(C3:C7,{"MD","MN"}))=5),"Min. Amt / 300","")),"")
D1	=IF(I3="YES",IF(OR(SUM(COUNTIF(C3:C7,{"B","W"}))=1),"Min. Amt / 286",IF(OR(SUM(COUNTIF(C3:C7,{"MD","MN"}))=1),"Min. Amt / 300","")),"")