I have two tables, a list of courses and a list of people who have booked on to those courses and I am trying to add a column to the first table which will be a sum of the items in the second who match a reference, taken away from how many spaces are available on that course.

EG Table1

Ref | Course | Spaces

01 | Excel | 10

02 | Word | 10

03 | Office | 5

Table 2

Name | Ref

Jon | 01

Jane | 01

Mark | 02

So now I want to add a column "RemainingSpaces"

New Table:

Ref | Course | Spaces | RemainingSpaces

01 | Excel | 10 | 8

02 | Word | 10 | 9

03 | Office | 5 | 5

I tried

AddColumns(Table1,"RemainingSpaces",Sum(Spaces,-CountIf(Table2, Ref = Ref)))

and it just seems to sum the whole second table.

Using [@Ref] just doens't work. I can't figure out how to get the calculation to use the reference from the list item it is in.

Any help much appreciated.