Sum-If with Multiple Criteria

[jjramirez85]

Hello,

In column C, I want the following action: forward calculation if the criteria is met but reverse calculation if other criteria is met. This is a two-way action.
[TABLE="width: 448"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD="colspan: 7"]if from dept = "Step 1" & to dept ="Step 2", then QTY * WEIGHT[/TD]
[/TR]
[TR]
[TD="colspan: 7"]if from dept = "Step 2" & to dept ="Step 1", then QTY * WEIGHT (negative)[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 618"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FROM DEPT[/TD]
[TD]TO DEPT[/TD]
[TD]QTY[/TD]
[TD]WEIGHT[/TD]
[TD]TOTAL WEIGHT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]STEP 1[/TD]
[TD]STEP 2[/TD]
[TD]10[/TD]
[TD]0.5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]STEP 2[/TD]
[TD]STEP 1[/TD]
[TD]5[/TD]
[TD]0.75[/TD]
[TD]-3.75[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Not sure why you are repeating columns A, B, and C, I am proceeding with the assumption that QTY is in column D, WEIGHT is in column R, and TOTAL WEIGHT is in column E.

Then, in E2 enter this formula:

=IF(AND(B2="STEP 1",C2="STEP 2"),C2*D2,IF(AND(B2="STEP 2",C2="STEP 1"),-C2*D2,""))