New Contributor

# anyone solve this formula problem please

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?

3 Replies

# Re: anyone solve this formula problem please

``=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.

# Re: anyone solve this formula problem please

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

# Re: anyone solve this formula problem please

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