Formula for conditional sum for distinct values

New Contributor

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.


Screenshot 2021-12-29 144930.jpg


5 Replies


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

SUMIF function


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


Hope I was able to help you with this info.



I know I don't know anything (Socrates)

Thanks for your reply.
But John is hard-coded in the formula. Is there anyway where the name could be dynamic?
best response confirmed by Hans Vogelaar (MVP)
=SUMIF(B2:B5, A1, C2:C5)
Got it, thanks a lot
I am pleased that this suggested solution has helped you.

We wish you much success and fun with Excel.