[samanthastone]

I am running Excel 2003. I have three worksheets. We will call them Worksheet A B and C. A is my permante worksheet the names do not change, however the numbers do. B and C are much shorter lists but the names in them do exsist in A. I need Excel to find the names from B and C and add the numbers that corrolate with the names into A.

The only way I can example this is as follows:

Original List A:
Sunny 20
Jack 15
Billy 10
Karen 40
James 35



List B:
Sunny 5
Karen 10


List C:
Jack 5
Billy 10
Karen 15


What I would like new list A to say:


Sunny 25
Jack 20
Billy 20
Karen 65
James 35


The real lists I have are much more complicated then this but I really need help. As of now I am adding hundreds of numbers manually!
Thank you for anything!!!!!!!!!!!!!!!!!

[Ziggy1]

Try this example out, it does what you want by pulling in the other sheet quantities and then adding them all together.

[slurpee55]

Just what I made up Ziggy. samantha, you can hide columns B, C, and D if you want to show only the names and the Totals. If Ziggy has answered your question, please use the button at the top of the page to mark this thread as Solved. And welcome to the Forum!

[samanthastone]

Hey guys!
That helped... but I am not a computer person. Can one of you put this formula into some sort of english so that I know where to change it to imput into the new excel sheet. Also where in the formula dictates if I am adding or subtracting?
Thank you guys sooooo much I am very greatful!

[Ziggy1]

The total column is doing the actual adding with a basic "Sum Formula". The formula is Vlookup but i added an "iserror" because when a Vlookup doesn't find a match it will display "#N/A"... I will explain later in more detail as I am heading out.... upload a sample file and show where you want it.

[slurpee55]

Ziggy stepped out but I just got back, so I can pitch in.
First, let's start with the formula in column E.
=SUM(B2: D2) (I had to add a space in the SUM formula because it came out as this
=SUM(B22) when the colon and capital d are next to one another.)
Pretty simple. It adds together (sums) the values in the cells of the range B2 to D2 - that is, it does this: B2+C2+D2.
The others are all variations on the same formula, they vary in what cell they are interested in and on what other worksheet they look at for information to retrieve. The formula:
=IF(ISERROR(VLOOKUP(A2,B!A:B,2,FALSE)),"",VLOOKUP(A2,B!A:B,2,FALSE))
can be thought of as two parts.
=VLOOKUP(A2,B!A:B,2,FALSE)
tells Excel to look vertically for what is in A2 (in this case it is "Sunny") on the other worksheet called "B"(hence the "B!" part of the formula) (but in the same file - looking in another file requires more information) and on sheet "B" it is supposed to look at all of columns A through B for a match to what is in A2. If it finds it, it is to return what is in the second column (the "2" part of the formula). The "FALSE" tells Excel that the match is to be identical - a nearly correct match is not to be accepted (this might seem odd with names, but with numbers this can be very important and is a good habit to get into.)
Now if no match was found, using only that part of the formula would return an error message of this sort: #N/A. Not horrible, but avoidable so let's do it.
The first part of the equation:
=IF(ISERROR(VLOOKUP(A2,B!A:B,2,FALSE)),""....
tells Excel that if the
=VLOOKUP(A2,B!A:B,2,FALSE)
part of the equation returns an error message then it should show what is called a null (the "" after the comma) - and if it isn't an error, then it should show what the VLOOKUP part found.
It may not seem clear at first, but tinker with it some and you will see how it works.

[samanthastone]

Thank you guys so much! You all rock and made my life 100% easier. I am going to keep on playing with this formula. It is wonderful!!!!!!!!!!!!!!!!!
Thank you sooo much I really really appreciate it!

[slurpee55]

Excel is a fantastic program...if you are just starting (ah, I remember those days) then you have so much cool stuff to learn...but the neatest part is, after over 10 years I am at best moderately good. Maybe you will be our next guru!
See you around.

[Ziggy1]

Originally Posted by slurpee55

Ziggy stepped out but I just got back, so I can pitch in.

lol, I feel like were on different shifts...thanks for covering You saved me a lot of typing...good job explaining

[slurpee55]

LOL! Actually, since you are in Ontario, you should have gone before me - I had been on lunch. I'm pretty much due south of Winnepeg.
And I used to teach persons with learning disabilities...kinda ingrained to break things down, even when I don't need to.