Hi Guys,


So I am having a problem. I am trying to create an inventory system where I can look up the same item with different quantities and add those quantities together on a separate spreadsheet.

Example.

Spreadsheet 1
Item info: Location Manufacture N: Quantity: Link:
Apples - CA - 45231 - 2
Bananas - CA - 6421 - 4
Pears - NY - 5648 - 2
Apples - CA - 45484 - 6
Apples - NY - 5648 - 3

Spreadsheet 2
Should pull all Item Info and Location plus Quantity (calculated) and the quantities and add them together

Item Info Location quantity
Apples - CA - 8
Apples - NY - 3
Bananas - CA - 4
Pears - CA - 2

If someone can help me with the formula for this that would be great Thank You

See the attached.

Attached Files

Inventory.xlsx (15.2 KB, 12 views)

Thank you Xor,
I tried implementing it into my spreadsheets however it caused problems, I may have messed up the equation while doing it
Also on your equation, it does not eliminate the second Apple but created a duplicate that was located in CA.


Your output was

ApplesCA - 8
ApplesNY - 3
BananasCA - 4
ApplesCA - 8
PearsCA - 2

Why not just use a pivot table!

Attached Files

Inventory.xlsx (12.5 KB, 15 views)

@Yasemin

Please write back should you want a formula solution.
Otherwise I will take it that jeffeybrown has solved the problem.

Hi Xor, I changed the spreadsheet to make it a little simpler, I can't figure out how to find the total of two items I am attaching a screenshot for you to look at.

This is what I am trying to do:

If Current Inventory = Used Inventory then the Quantity of Current Inv. - Quantity of Used Inv. = Total
HP Notebook 2 - HP Notebook 1 = 1

Note:

$D:$D has been fixed from $D: D still the same result

Attached Images

equation.jpg (116.9 KB, 21 views)

Can't you upload the Excel file and give examples (3-5) of expected results.

Here is a sample one. Thank You

Attached Files

Example.xlsx (163.0 KB, 8 views)

I am in doubt if this is what you are after. If not please describe more precisely what you want.

Attached Files

Inventory_example.xlsx (169.6 KB, 16 views)

Thank You, this is what I was looking for but did had couple glitches maybe you can help me with this one.

Attached Files

Inventory_example.xlsx (164.9 KB, 9 views)

See the attached.

Attached Files

Inventory.xlsx (177.1 KB, 9 views)

Thank You so much this works, I used the coding and it was able to identify and subtract the information. Again thank you so much for helping me out.

I’m happy to help.