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

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]

Read my post #10 .

 

[jgold20]

I tried both examples and both returned blanks. To answer your question:

At any given time, there will be many different values in C3:C7. I only want to display divide by 286 for "B" and "W" and divide by 300 for "MD and "MN". For the time being, all other alpha values in C3:C7 can default to a message divide by 200

 

[jtakw]

Won't be able to help you any further tonight (3 am where I am). Might be back on sometime tomorrow.

It would Really help if you explain what you're trying to do for your helpers to help you.

We don't know what you're working on, we don't know what you're thinking, we don't know the lay out of your data, etc. We can only rely on what you Tell Us, to try to Help You solve Your Problem.

 

[jtakw]

I tried both examples and both returned blanks. To answer your question:

There can be many different values in C3:C7 at any given time. I only want to display divide by 286 for "B" and "W". I want to divide by 300 for "MD and "MN". For the time being, all other alpha values in C3:C7 can default to a message divide by 200

Ok, 15 more minutes.

So what happens if C3 is "B", and C4 is "MD"?

Are you looking for a result per LINE?

 

[jtakw]

Please post a sample table like your Post #9 WITH ALL expected results.

 

[jgold20]

C3:C7 will have an alpha value at all times. They will never have the same alpha value.
After giving the problem additional thought:

1) c3:c7 = one of the above (MD, MN, B, W), display appropriate / message
2) c3:c7 = two of them at the same time (can happen more than once in c3:c7) (MD, MN), (B, W), display both(286, 300) / by messages.
3) Number 2 above plus c3:c7= any other alpha combination, display - / 286, /300 and / 200

[TABLE="width: 675"]
<tbody>[TR]
[TD="class: xl74, width: 95, align: center"]Company Code[/TD]
[TD="class: xl92, width: 183, colspan: 2, align: center"]Special Instructions[/TD]
[TD="class: xl74, width: 105, align: center"]Enter Min. Move Amt.[/TD]
[TD="class: xl79, width: 95, align: center"]Min.Price per CF [/TD]
[TD="class: xl74, width: 104, align: center"] Filing OR Scheduling & CC Fees[/TD]
[TD="class: xl80, width: 93, align: center"]Minimum Move[/TD]
[/TR]
[TR]
[TD="class: xl84, align: center"]B[/TD]
[TD="class: xl91, width: 183, colspan: 2, align: center"]to West: NM,AZ,CA,ND,OR, WA,SD,WA,ID,WY,CO,NE[/TD]
[TD="class: xl88, width: 105, align: center"] $ 1,500.00[/TD]
[TD="class: xl89, align: center"] $ 5.24[/TD]
[TD="class: xl85, align: center"]NO[/TD]
[TD="class: xl94, width: 93, align: center"]YES[/TD]
[/TR]
[TR]
[TD="class: xl86, align: center"]L[/TD]
[TD="class: xl93, width: 183, colspan: 2, align: center"][/TD]
[TD="class: xl88, width: 105, align: center"] $ 1,500.00[/TD]
[TD="class: xl87, align: center"] $ -[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl95, width: 93, align: center"]Message here[/TD]
[/TR]
[TR]
[TD="class: xl77, align: center"]N[/TD]
[TD="class: xl91, width: 183, colspan: 2, align: center"][/TD]
[TD="class: xl88, width: 105, align: center"] $ 1,500.00[/TD]
[TD="class: xl83, align: center"] $ -[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl75, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl77, align: center"]W[/TD]
[TD="class: xl91, width: 183, colspan: 2, align: center"][/TD]
[TD="class: xl88, width: 105, align: center"] $ 1,500.00[/TD]
[TD="class: xl83, align: center"] $ 5.24[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl75, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl78, align: center"][/TD]
[TD="class: xl90, width: 183, colspan: 2, align: center"][/TD]
[TD="class: xl82, width: 105, align: center"][/TD]
[TD="class: xl81, align: center"] $ -[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl75, align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[jgold20]

If c3=b, c4=md - "Min Amt/286 or /300

looking for results per line

 

[jgold20]

Depending on a drop down selection, c3:c7 will have different values

 

[markmzz]

Hi!

If I understand correctly what you want, maybe the formula below can helps:

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

Or

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

Markmzz

 

[jgold20]

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