# Trying to calculate damaged stock for individual items in excel

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

• 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 '12 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 '12 at 17:34

``````=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

• 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 '12 at 17:58
• Thanks charlie. I have updated my answer with more explanation – Siva Charan Jan 4 '12 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 '12 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 '12 at 0:10
• You sir, are a legend - thanks for the answer! – falter Jan 5 '12 at 10:57