# Value Error - if and statement

jgold20

Board Regular
Joined
Feb 4, 2018
Messages
135
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","")
 
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?
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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