SUMIF column in column's row, is the same as another column

Question

I have two sheets.

Income (Sheet1):

A          | B      | C
Job number | Amount | Net
1          | 100    | 
2          | 200    | 
3          | 100    | 
4          | 60     |
5          | 100    | 
6          | 100    | 
7          | 100    | 
8          | 100    |

And

Outcome (Sheet2):

A          | B      | C
Job number | Amount | Title
1          | 5      | Stationery
2          | 15     | Printer ink
2          | 10     | et 
2          | 10     |
4          | 20     | 
6          | 30     | 
8          | 10     | 
8          | 10     |

How can I write a formula for Sheet1 Column C, to sum values of Sheet2 Column B, where Sheet2 ColumnA is equal to the job number found in the corresponding Sheet1 Column A? That way I can deduct this amount from the income amount, the result being as below:

Income (Sheet1)

(Working shown next to desired result)

A          | B      | C
Job number | Amount | Net
1          | 100    | 95         (100 - 5)
2          | 200    | 165        (200 - 15 - 10 - 10)
3          | 100    | 100        (100 - nothing)
4          | 60     | 40         (60 - 20)
5          | 100    | 100        (100 - nothing)
6          | 100    | 70         (100 - 30)
7          | 100    | 100        (100 - nothing)
8          | 100    | 80         (100 - 10 - 10)

Its nearly this question Count cells based on a comparison with value in the same row of another column but not quite... !

Answer 1

You can do this with a tailor-made SUMIF formula, sure, but I love PivotTables. PivotTable skills translate to a much more user-friendly experience without learning (relatively) complicated formula syntax.

Select the data (click inside, hit CTRL-A) of Sheet 2 and go to Insert > PivotTable. You probably want to create this on a new sheet. If/when you add more data to the source table, you can find the methods to refresh/expand the pivot table after clicking within it and finding PivotTable Options on the ribbon.

From this point you can arrange the pivot table as shown here (the markup I did was just for visual reference):

By doing this you have condensed your data to a single row per job number without changing your source data. From this, you can use a dead-simple VLOOKUP function to do your math:

=B2-VLOOKUP(A2,Sheet4!A:B,2,FALSE)

But we have a problem here! We're getting #N/A because some of our jobs do not have entries on Sheet 2. The easy fix here is to just account for errors, like so:

=IFERROR( our formula , original amount)

If you're just starting out using PivotTables, you're probably more comfortable using basic functions like Vlookup and addition/subtraction, so this method should be more helpful in developing good Excel skills.

Answer 2

=SumIf(Sheet2!$A$2:$A$9,$A2,Sheet2!$C$2:$C$9) will Sum the values on sheet 2, based on Col A in Sheet 1.

Then, you could just do this in your col. C in Sheet1:

=$B2-SumIf(Sheet2!$A$2:$A$9,$A2,Sheet2!$C$2:$C$9)