# Solved: Adding Selected Values Together Over Multiple Work-Sheets In Microsoft Excel

Discussion in 'Business Applications' started by blujein, Aug 11, 2008.

Not open for further replies.

Joined:
May 12, 2008
Messages:
158
G'day,

I have a Microsoft Excel 2003 file that contains two spread-sheets. In one, I have a list of clients and a column next to each client name that I want to display the amount of money made from that particular client. Then, on the second spread-sheet I have a list of all credit and debits relating to the various clients. So, we might have received \$100.00 from a client (and that would be in the credit column next to the client name), and then we may have spent \$50.00 on that client (and that would be in the debit column next to the client name). So, obviously, the total amount made from that client would reflect the credit minus the debit.

Anyway, what I need is for the cell on the first spread-sheet that says the total made for that client to look at the second spread-sheet, look for any row that has that client's name and then adds the credits and subtracts the debits, then leaving the total back on the first spread-sheet.

I hope that makes sense. I have attached a demonstration to help, and I have also done the formula for the first client to show you what outcome I am looking for (although the formula is not what I want because it doesn't automatically add all of the rows from the second spread-sheet that share that client name).

Anyway, I hope I've explained it well enough and if anyone can help me I'd really appreciate it!

File size:
16.5 KB
Views:
106
2. ### Yorkshire Guy

Joined:
Dec 9, 2003
Messages:
563
hi blujein,

Attached is a quick solution.

Copy the formula I have added to the Total Amounts spreadsheet in Col B, down in new rows as you add new clients.

lol
Hew

File size:
17.5 KB
Views:
152
3. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Hew's answer is fine, as ever.

I'm just curious at to why you have 2 columns on Logged Amounts? Why not have a single "Amount" column with positive values for "Made" items and negative values for "Spent" items? Then you'd only need one SUMIF.

Joined:
May 12, 2008
Messages:
158
That is perfect! Thank you so much for your help!

Essentially, I do it this way because I want to be able to see what's been made and what's been spent with each client in a single glance. Having it this way enables me to instantly get a quick idea as to what, on average, we're doing more for for each client. If there's a better way of going about it, though, I'd be very interested in hearing it.

As Seen On