 SOLVED

Formula for conditional sum for distinct values

I have 3 columns in a table - From, To, and Amount. From and To field contains names. The logic is if a name features in the From section, it would mean money is paid by that person and if the name features in the To field, it would mean money is received. I wish to calculate sum total of receipts/ payments for each distinct name. Is this possible through a formula?
I am attaching an excel sheet showing the input and the output that I wish to achieve. 5 Replies

Re: Formula for conditional sum for distinct values

The most elegant approach would be with pivot, but you could also do it with a simple SUMIF function.

SUMIF function

Tips:

• If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."

• To sum cells based on multiple criteria, see SUMIFS function.

Nikolino

I know I don't know anything (Socrates)

Re: Formula for conditional sum for distinct values

But John is hard-coded in the formula. Is there anyway where the name could be dynamic?
best response confirmed by Hans Vogelaar (MVP)
Solution

Re: Formula for conditional sum for distinct values

=SUMIF(B2:B5, A1, C2:C5)

Re: Formula for conditional sum for distinct values

Got it, thanks a lot

Re: Formula for conditional sum for distinct values

I am pleased that this suggested solution has helped you.

We wish you much success and fun with Excel.