anyone solve this formula problem please

[Torren1]

Hi

need some help with a formula which will go in Table 1 Total Cost.

so I need a formula in table 1, row is Job No.1 in the blank cell under total cost, that can search through table 2 by the Job number & "ADD" from the add/remove column and if these two terms match to add all the costs up.

but when they add the cost up it needs to be done in this way. it will add the cost from the estimated cost but if the estimated cost is £0 it will use the Actual cost instead. so table 1, Job 1 total cost should read £28.

is anyone able to help me please?

Table 1					Table 2
Job Number	Add/remove	Total Cost					Add/remove	Job number	Estimated cost	Actual cost
1	Add						Add	1	10	8
2	Remove						add	1		8
3	Add						Add	1	10	0
							Remove	2		10
							Remove	2		10
							Add	3		10

[#2]

@Torren1 

=SUMPRODUCT((B3=$H$3:$H$8)*(A3=$I$3:$I$8)*IF($J$3:$J$8=0,$K$3:$K$8,$J$3:$J$8))

Is this what you are looking for? Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021. 

[#3]

yes thank you this is great.

just need to make one change i forgot to say if the Add/remove column if it is a "Remove" i need it to be £0

[#4]

Hi @Torren1 

Excel 2021/365 variant

in D3

=IF([@[Add/Remove]] = "remove", 0,
    SUM(
        FILTER(
            SWITCH(Table2[Estimated Cost],0,Table2[Actual Cost],Table2[Estimated Cost]),
            (Table2[Add/Remove]=[@[Add/Remove]]) * (Table2[Job Number]=[@[Job Number]]),
            NA()
        )
    )
)