up vote 0 down vote favorite
share [g+] share [fb]

I have a worksheet (sheet 1) which looks like this:

item name | quantity | sold | damaged stock 
keyring   | 100      | 20   |  5
ball      | 100      | 55   |  1

and another worksheet (sheet 2), which is for damaged stock recording, and looks like this:

damaged date | item name | quantity damaged | type of damage | 
01-01-2012   | keyring   |    2             | water          |
02-01-2012   | ball      |    1             | fire           |
03-01-2012   | keyring   |    3             | robot attack   |

Now what I want to do is calculate the total number of individual damaged items in sheet 2 and add them to sheet 1 automatically (ie for keyrings, 2+3 in sheet 2 = the 5 in sheet 1)

Really appreciate the help! thanks

link|improve this question
43% accept rate
Just link the contents of the cell on sheet 1 to the contents of any cell on sheet 2. Use similar context as link a cell just add which sheet its on. For example =Sheet1!A1 – Ramhound Jan 4 at 17:12
Not quite that simple - he'll need to make the summing dependent on the content of cell 'item name'. This is more of a database application (item name in sheet 2 being a foreign key), which you can totally do in Excel, but unfortunately I don't know how. – jcrawfordor Jan 4 at 17:34
feedback

1 Answer

up vote 3 down vote accepted 
=SUMIF(DamagedStock!B:B, A2, DamagedStock!C:C)

If you want answer in-terms of default sheet names, refer below

=SUMIF(Sheet2!B:B, A2, Sheet2!C:C)

Description:

DamagedStock - It is referring to Sheet2
A2 - It is referring to Sheet1

DamagedStock!B:B - Contains Stock Item Names
A2 - Unique Stock Item Name of Column A in Sheet1
DamagedStock!C:C - Contains Quantity Damaged Info
B:B - It represents whole Item Names column on Sheet2
C:C - It represents whole Quantity Damaged column on Sheet2

link|improve this answer
Good solution, but you might want to explain where the formula goes and what the tab references are. If they cut and paste your formula it will only work if they have the tabs named the differently. It appears they left them as default (sheet1 & sheet2). – CharlieRB Jan 4 at 17:58
Thanks charlie. I have updated my answer with more explanation – Siva Charan Jan 4 at 18:21
This is a smooth solution (+1) and a good use of an under used function but you still haven't said that the formula is pasted into Sheet1!D2 and then copied down as CharlieRB suggested. My only criticism is that if a product is recorded in Sheet2 but not in Sheet1 then no warning is given. This would only matter if, for example, kevyn's system allows product names to be misspelt. – Tony Dallimore Jan 4 at 20:18
I should add that, although I like this solution, I have a VBA solution if one is needed. – Tony Dallimore Jan 5 at 0:10
You sir, are a legend - thanks for the answer! – kevyn Jan 5 at 10:57
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.