4 IFs

[hsandeep]

A1=1 or ""null (thr' formula)
B1=HOLD or null (thr' formula)

If:
A1=1 and B1=HOLD; C1=100
A1=1 and B1=""null; C1=200
A1=""null and B1=HOLD; C1=300
A1=""null and B1=""null; C1=400
How to get either of C1?

 

[Misca]

Try putting your possible combinations into a table of 4 rows x 3 columns (1st column for A, 2nd for B and 3rd for C values).

Once you've done that you can use SUMPRODUCT to get the C1 values.

Say my table was located in F1:H4 so the whole function in C1 would be

=SUMPRODUCT(--(F1:F4=A1),--(G1:G4=B1),H1:H4)

 

[Marcol]

Try this ...

=IF(AND(A1=1,B1="HOLD"),100,IF(AND(A1=1,B1=1),200,IF(AND(A1=1,B1=""),200,IF(AND(A1="",B1="HOLD"),300,IF(AND(A1="",B1=""),400,"")))))

 

[Jonmo1]

A1=1 or ""null (thr' formula)
B1=HOLD or null (thr' formula)

If those are truely the only 2 possible results of each cell, then try

=MATCH(LEN(A1&B1),{5,1,4,0},0)*100

 

[hsandeep]

=IF(AND(A1=1,B1="HOLD"),100,IF(AND(A1=1,B1=1),200,IF(AND(A1=1,B1=""),200,IF(AND(A1="",B1="HOLD"),300,IF(AND(A1="",B1=""),400,"")))))

[/QUOTE]
This works.