Inventory Spreadsheet Question

[Guest]

I need to develop a "current inventory" spreadsheet from two existing
spreadsheets; one tracks add and the other tracks substractions/sold. Each
spreadsheet tracks product, size and quantity. Here's an example:

Product Size Quantity
Blue Pants Lrg 5
Red pants Sm 5
Blue Pants Med 6
Blue Pants Lrg 7

Information is entered into these spreadsheets as events happen. So
multiple similar product/size combinations exist in both spreadsheets. My
challenge is adding up all the matching combinations in the two spreadsheets
and then substracting the solds from the add. Also need to consider that
some product/size combinations have never sold - so these combinations would
not appear on the sold spreadsheet.

Thanks in advance for any suggestions.

 

[Roger Govier]

Hi

On you summary sheet create a unique list of Products and sizes.
Assuming your sheets are called"Add" and "Subtract", and that the values
entered in quantity column are positive on both sheets, enter the
following formula into cell C2 and copy down

=SUMPRODUCT((Add!$A$2:$A$100=A2)*(Add!$B$2:$B$100=B2)*(Add!$C$2:$C$100))-SUMPRODUCT((Subtract!$A$2:$A$100=A2)*(Subtract!$B$2:$B$100=B2)*(Subtract!$C$2:$C$100))

If the values on your Subtract sheet are negative, then add the second
sumproduct formula to the first.

 

[Guest]

A very common approach is to ue a Stock Number. This is a product number you
assign to each product. Say Blue Pants Large is 100; Blue Pants Medium is
101, etc.

The Purchase ( or add) spreadsheet might look like:

100 500
101 500
102 500
103 500
104 500

with a new entry made whenever we add. The total added to-date is:
=SUMPRODUCT((B1:B100)*(A1:A100=100))
for product #100 which is 500 items


The Sold Sheet might look like:
106 1
104 3
104 3
104 1
103 3
104 3
100 2
110 4
110 3
101 2
105 3
108 3
107 5
107 2
107 4
100 1
102 3
108 2
100 5
103 3
with a new entry made whenever we sell. The total sold to-date is:
=SUMPRODUCT((B1:B100)*(A1:A100=100))
for product #100 which is 8 items

The inventory is the difference in these two formulas


There are also many inventory templates available free from Microsoft:

http://office.microsoft.com/en-us/templates/CT101172541033.aspx

 

[Guest]

Roger-

Thanks for the suggestion, I can see how this would work in most cases. I
should have mentioned my summary worksheet will be set-up slightly different
from the add/subtract worksheets.

The summary needs to look like this:

Product/Size sm med lrg x-lrg
blue pants
red pants
yellow pants

How should the formula be changed to reflect this format?

Thanks again.

 

[Roger Govier]

Hi

Try
=SUMPRODUCT((Add!$A$2:$A$100=$A2)*(Add!$B$2:$B$100=B$1)*(Add!$C$2:$C$100))-SUMPRODUCT((Subtract!$A$2:$A$100=$A2)*(Subtract!$B$2:$B$100=B$1)*(Subtract!$C$2:$C$100))

Copy across and down.

 

[Guest]

Roger-

That worked - thanks so much!

Hi

Try
=SUMPRODUCT((Add!$A$2:$A$100=$A2)*(Add!$B$2:$B$100=B$1)*(Add!$C$2:$C$100))-SUMPRODUCT((Subtract!$A$2:$A$100=$A2)*(Subtract!$B$2:$B$100=B$1)*(Subtract!$C$2:$C$100))

Copy across and down.