# Total of column if adjacent cell is true while subtracting from another column

I have data like in the following table:

``````+---+-----+-----+
| A | B   | C   |
+---+-----+-----+
| 0 | 5   | 30  |
| 1 | 0   | 30  |
| 0 | 15  | 40  |
| 1 | 0   | 40  |
| 1 | 10  | 50  |
+---+-----+-----+
``````

Column A is a flag (true = 1, false = 0) so we only add up rows with flag==1
Column B is a "tax" to remove from the amount in Column C
So basically here, the total would be 110:

``````(we don't count row 1)
30-0
(we don't count row 3)
40-0
50-10
``````

If you add the following formula in `C7` with sum written in `B7`, then the total will be calculated the way you want:

`=SUM(ARRAYFORMULA(IF(A2:A6=1;C2:C6-B2:B6,0)))`

I would add a fourth column for the calculated line item result (using an `if` statement) and then do a simple `=sum()` at the end.

Make a new column to the right of C. Add this `if` statement:

``````=if(A1=0, C1, C1-B1)
``````

Then sum column C.

• Your total equals 180 and not 110 !! The formula should have been like this: `=if(A2=0, 0, C2-B2)`. – Jacob Jan Tuinstra Jun 2 '13 at 17:51