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.
What is wrong with the formula you have already? Are you trying to generate the entire table or just the entries?
Administrative Note:
Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Administrative Note:
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. No help to be offered until the link is provided.)
Oh! I didn't know this. Let me check how to post cross post links
Post the link with one or two spaces in it, please. And then go and read the rules (which are the same in this respect on Chandoo, by the way): https://www.excelforum.com/forum-rul...rum-rules.html
Nothing wrong but the list in the output table is static (and not dynamic) which means that one has to manually change the list everytime there is a new entry.
Also, it would be more readable if I can just get the total values for each distinct name. I think this could be achieved with SQL - I will give this a try.
Error message: You are not allowed to post any kinds of links, images or videos until you post a few times.
READ what I said in post #5!!!
As you are clearly struggling, here is the required link: https://chandoo.org/forum/threads/fo...-values.47366/
Please make sure that you do this yourself in future if you cross post again.
If you just want to get the totals, then you can use this formula (put it in cell G36 and then you can compare it with your totals on row 34 more easily):
=SUMIF($C:$C,G$2,$D:$D)-SUMIF($B:$B,G$2,$D:$D)
Copy across as required. Although most of the totals agree with yours, there are some discrepancies - I have not investigated why.
Hope this helps.
Pete
Your table is missing the following letters along row 2: C, A, W, G, T, and D.
Also, you have 3 rows where the same letter appears in both columns B and C, i.e. rows 6, 20 and 22. Your formula doesn't account for these situations, so you need to change it in G3 to this:
=IF($B3=G$2,-$D3,0) +IF($C3=G$2,$D3,0)
then copy across and down as required.
Hope this helps.
Pete
You have said on the other forum that this is now resolved, so please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks