IF/OR/AND function in a formula

[Rumy]

I have a unique scenario and need assiatance to create a formula.

I have dollar amounts in A,B,C,D,E,F columns. I need a result in G

it should be A+B+C. If A+B+C equals 0 the result should be D. If D also
equals 0 then the result should be E - F. Please help. Thanks.

 

[Joe User]

I have dollar amounts in A,B,C,D,E,F columns. I need a result in G[.]
it should be A+B+C. If A+B+C equals 0 the result should be D. If D
also equals 0 then the result should be E - F. Please help.

=if(A1+B1+C1<>0, A1+B1+C1, if(D1=0, E1-F1, D1))

 

[Luke M]

=IF(SUM(A2:C2)=0,IF(D2=0,SUM(E2:F2),D2),SUM(A2:C2))

 

[Joe User]

PS....

I have dollar amounts in A,B,C,D,E,F columns.

If you mean that you have dollar-and-cents amounts with mixed signs (some
positive, some negative), or if the "dollar" amounts are the result of
formulas formatted with zero decimal places, note that what appears to be
zero is not always exactly zero.

So it would be prudent to write:

=round(if(round(A1+B1+C1,2)<>0, A1+B1+C1, if(D1=0, E1-F1, D1)), 2)

Change D1=0 to ROUND(D1,2)=0 if you did not round the formula in D1.


----- original message -----

I have dollar amounts in A,B,C,D,E,F columns. I need a result in G[.]
it should be A+B+C. If A+B+C equals 0 the result should be D. If D
also equals 0 then the result should be E - F. Please help.

=if(A1+B1+C1<>0, A1+B1+C1, if(D1=0, E1-F1, D1))

 

[Minty Fresh]

Hi Rumy
This is what I got:
=IF(AND(SUM(A2:C2)=0,D2=0),E2-F2,IF(SUM(A2:C2)=0,D2,SUM(A2:C2)))

If I am reading the problem correctly,
Luke's formula is shorter and, therefore, more elegant, but it adds E and F,
rather than subtracting F from E.
With the following revision, I like Luke's better:
=IF(SUM(A2:C2)=0,IF(D2=0,E2-F2,D2),SUM(A2:C2))
The both test out the same.
M

 

[Rumy]

Thank you both Luke and Minty Fresh That was a great help.