SOLVED

Need help with complex Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3217835%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20complex%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3217835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excelhelp1225_0-1645958920158.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351500i2379F5F922243E36%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Excelhelp1225_0-1645958920158.png%22%20alt%3D%22Excelhelp1225_0-1645958920158.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20to%20sum%20columns%20B%20and%20C%20for%20a%20given%20row%20and%20then%20subtract%20the%20above%20values%20in%20column%20D%20only%20for%20the%20rows%20that%20have%20the%20same%20value%20in%20column%20A.%20See%20column%20D%20for%20desired%20outcome%20of%20formula.%20Thanks%20in%20advance%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3217835%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-3217989%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20complex%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3217989%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1318967%22%20target%3D%22_blank%22%3E%40Excelhelp1225%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20D2%3A%3C%2FP%3E%0A%3CP%3E%3DB2%2BC2-SUMIF(%24A%241%3A%24A1%2CA2%2C%24D%241%3A%24D1)%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3218053%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20complex%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3218053%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EIn%20addition%20to%20Mr.%3CSPAN%20class%3D%22%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3EHans%20Vogelaar's%26nbsp%3B%3C%2FA%3E%26nbsp%3B%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3Esuggestion%2C%20here%20is%20an%20extension%20for%20the%20sums%20of%20the%20individual%20numbers%20in%20A.%3C%2FSPAN%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CH4%20id%3D%22toc-hId-1089396714%22%20id%3D%22toc-hId-1089374577%22%20id%3D%22toc-hId-1089374577%22%20id%3D%22toc-hId-1089374577%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SumIf_Test.JPG%22%20style%3D%22width%3A%20642px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351507iE28ECBC212395012%2Fimage-dimensions%2F642x397%3Fv%3Dv2%22%20width%3D%22642%22%20height%3D%22397%22%20role%3D%22button%22%20title%3D%22SumIf_Test.JPG%22%20alt%3D%22SumIf_Test.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FH4%3E%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1318967%22%20target%3D%22_blank%22%3E%40Excelhelp1225%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3218235%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20complex%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3218235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThat%20worked%20beautifully!%20Thank%20you%20so%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Excelhelp1225_0-1645958920158.png

I need a formula to sum columns B and C for a given row and then subtract the above values in column D only for the rows that have the same value in column A. See column D for desired outcome of formula. Thanks in advance for your help.

5 Replies
best response confirmed by NikolinoDE (Respected Contributor)
Solution

@Excelhelp1225 

In D2:

=B2+C2-SUMIF($A$1:$A1,A2,$D$1:$D1)

Fill down.

In addition to Mr.Hans Vogelaar's  suggestion, here is an extension for the sums of the individual numbers in A. 

SumIf_Test.JPG

@Excelhelp1225

@Hans Vogelaar That worked beautifully! Thank you so much!!

Thank you! So grateful for your help!
Spoiler
 

@Excelhelp1225 

I can't help feeling that the original formula is expressed in a manner that is unnecessarily complex.  Any given number (being the sum of the two amount columns) appears in column D within the same row, appears negated in the next row, and from there on cancels out.

Using Charles Williams's FastExcel DIFF formula gives

= IF(DIFF(Nbr), AmtS, DIFF(AmtS,0))
where
AmtS = Amt₁+Amt₂

Without the Add-in formula, one can 'roll one's own' Lambda function 'Diffλ' defined by

= LAMBDA(x, LET(
  k, SEQUENCE(COUNT(x)),
  x - IF(k>1,INDEX(x,k-1))))

giving the worksheet formula

= IF(Diffλ(Nbr), AmtS, Diffλ(AmtS))

image.png