| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Spreadsheet Question
Roger-
That worked - thanks so much! "Roger Govier" wrote: 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. -- Regards Roger Govier "JWNJ" wrote in message ... 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" wrote: 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. -- Regards Roger Govier "JWNJ" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Inventory Spreadsheet | Excel Worksheet Functions | |||
| Invoice/Inventory spreadsheet | Excel Discussion (Misc queries) | |||
| excel inventory spreadsheet | New Users to Excel | |||
| How to start a spreadsheet (inventory) | New Users to Excel | |||
| Inventory spreadsheet | Excel Discussion (Misc queries) | |||
Hybrid Mode
