Feb 19 2022 09:11 AM
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 |
Feb 19 2022 09:46 AM
=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.
Feb 19 2022 10:15 AM
Feb 20 2022 02:01 AM
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()
)
)
)