Google Sheets – How to Total a Column if Adjacent Cell is True While Subtracting from Another Column

google sheets

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

How do I do it in Google Spreadsheet?

Best Answer

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)))

Related Topic