SOLVED

New Contributor

# Need help with complex Excel formula

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

# Re: Need help with complex Excel formula

In D2:

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

Fill down.

# Re: Need help with complex Excel formula

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

@Excelhelp1225

# Re: Need help with complex Excel formula

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

# Re: Need help with complex Excel formula

Thank you! So grateful for your help!

# Re: Need help with complex Excel formula

Spoiler

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