# 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]

In terms I think I can express now:

If C3:C7 = B, W display / 286
If C3:C7 = MD, MN display / 300
If C3:C7 = H, V, X, etc. display / 200
If C3:C7 = Any combination above(B, MD, H) display /286, /300, /200

What if B, MN, H all available in C3:C7?

 

[jgold20]

display /200 /286 /300

 

[Aladin Akyurek]

display /200 /286 /300

Thanks.

H or V or X or etc >> / 200. What does etc mean? any single letter other than B or W?

 

[markmzz]

In terms I think I can express now:
If C3:C7 = B, W display / 286
If C3:C7 = MD, MN display / 300
If C3:C7 = H, V, X, etc. display / 200
If C3:C7 = Any combination above(B, MD, H) display /286, /300, /200

In this case, try this:

=IF((I3="YES")*(COUNTIF(C3:C7,"?*")>0),"Min. Amt"&
IF(SUM(COUNTIF(C3:C7,{"B","W"}))," / 286","")&
IF(SUM(COUNTIF(C3:C7,{"MD","MN"}))," / 300","")&
IF(SUM(COUNTIF(C3:C7,{"B","W","MD","MN"}))=0," / 200","")&
IF(SUM(COUNTIF(C3:C7,{"B","W"}))*SUM(COUNTIF(C3:C7,{"MD","MN"}))*
(COUNTIF(C3:C7,"?*")-SUM(COUNTIF(C3:C7,{"B","W","MD","MN"})))," / 200",""),"")

And tell me the results.

Markmzz

 

[jgold20]

There can be various combinations in c3:c7

B, MD, MN, W, H

B, MD, L, N, X

MD, MN, Q, R, D

and so on

 

[Aladin Akyurek]

There can be various combinations in c3:c7

B, MD, MN, W, H

B, MD, L, N, X

MD, MN, Q, R, D

and so on

Thanks again.

Assumptions:

(1) Whatever is in C3:C7, we expect a single result.

(2) Either B or W in C3:C7, we get / 286

(3) Either MD or MN, we get / 300

(4) Any other single letter like H, L, N, X, Q, R, D, etc other than B or W, we get / 200

(5) If any combination of [1], [2], [3], or [4], we get / 286, / 300, / 200

Is the foregoing an exhaustive and correct summary?

 

[jgold20]

Assumptions:

(1) Whatever is in C3:C7, we expect a single result.

(2) Either B or W in C3:C7, we get / 286

Or both

(3) Either MD or MN, we get / 300

Or both

(4) Any other single letter like H, L, N, X, Q, R, D, etc other than B or W, we get / 200

Or all

(5) If any combination of [1], [2], [3], or [4], we get / 286, / 300, / 200

Correct

Is the foregoing an exhaustive and correct summary?

 

[Aladin Akyurek]

The rules so far imply that:

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

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

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

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

Are these correct implications?

 

[jgold20]

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

 

[jgold20]

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

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