anyone solve this formula problem please

%3CLINGO-SUB%20id%3D%22lingo-sub-3188072%22%20slang%3D%22en-US%22%3Eanyone%20solve%20this%20formula%20problem%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3188072%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eneed%20some%20help%20with%20a%20formula%20which%20will%20go%20in%20Table%201%20Total%20Cost.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20I%20need%20a%20formula%20in%20table%201%2C%20row%20is%20Job%20No.1%20in%20the%20blank%20cell%20under%20total%20cost%2C%20that%20can%20search%20through%20table%202%20by%20the%20Job%20number%20%26amp%3B%20%22ADD%22%20from%20the%20add%2Fremove%20column%20and%20if%20these%20two%20terms%20match%20to%20add%20all%20the%20costs%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20when%20they%20add%20the%20cost%20up%20it%20needs%20to%20be%20done%20in%20this%20way.%20it%20will%20add%20the%20cost%20from%20the%20estimated%20cost%20but%20if%20the%20estimated%20cost%20is%20%C2%A30%20it%20will%20use%20the%20Actual%20cost%20instead.%20so%20table%201%2C%20Job%201%20total%20cost%20should%20read%20%C2%A328.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20anyone%20able%20to%20help%20me%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22812%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22244%22%3ETable%201%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22312%22%3ETable%202%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJob%20Number%3C%2FTD%3E%3CTD%3EAdd%2Fremove%3C%2FTD%3E%3CTD%3ETotal%20Cost%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAdd%2Fremove%3C%2FTD%3E%3CTD%3EJob%20number%3C%2FTD%3E%3CTD%3EEstimated%20cost%3C%2FTD%3E%3CTD%3EActual%20cost%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EAdd%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAdd%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3ERemove%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eadd%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EAdd%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAdd%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERemove%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERemove%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAdd%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3188072%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3188153%22%20slang%3D%22en-US%22%3ERe%3A%20anyone%20solve%20this%20formula%20problem%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3188153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1210193%22%20target%3D%22_blank%22%3E%40Torren1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT((B3%3D%24H%243%3A%24H%248)*(A3%3D%24I%243%3A%24I%248)*IF(%24J%243%3A%24J%248%3D0%2C%24K%243%3A%24K%248%2C%24J%243%3A%24J%248))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%20Enter%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3189323%22%20slang%3D%22en-US%22%3ERe%3A%20anyone%20solve%20this%20formula%20problem%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189323%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1210193%22%20target%3D%22_blank%22%3E%40Torren1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%202021%2F365%20variant%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349750iDBB37C7D988A9E80%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot.png%22%20alt%3D%22Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3ED3%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%5B%40%5BAdd%2FRemove%5D%5D%20%3D%20%22remove%22%2C%200%2C%0A%20%20%20%20SUM(%0A%20%20%20%20%20%20%20%20FILTER(%0A%20%20%20%20%20%20%20%20%20%20%20%20SWITCH(Table2%5BEstimated%20Cost%5D%2C0%2CTable2%5BActual%20Cost%5D%2CTable2%5BEstimated%20Cost%5D)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(Table2%5BAdd%2FRemove%5D%3D%5B%40%5BAdd%2FRemove%5D%5D)%20*%20(Table2%5BJob%20Number%5D%3D%5B%40%5BJob%20Number%5D%5D)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20NA()%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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 NumberAdd/removeTotal Cost    Add/removeJob numberEstimated costActual cost
1Add     Add1108
2Remove     add1 8
3Add     Add1100
       Remove2 10
       Remove2 10
       Add3 10
3 Replies

@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. 

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

Hi @Torren1 

 

Excel 2021/365 variant

Screenshot.png

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()
        )
    )
)