# Excel Stock Inventory/Sales Workbook

1. ## Excel Stock Inventory/Sales Workbook

Hi all,

I have a workbook with 2 worksheets - Sales and Inventory (attached an example). The sales sheet documents all sales made by my company, with cost and quantity of the items sold, and the inventory includes how many of each we have in stock. I want this inventory to automatically update with the sales.

Example:

In the example workbook attached, we are selling tennis balls, footballs and rugby balls (ignore the cost) - we have 2 separate sales, each with different quantities for the different balls. On the inventory page, BEFORE taking those sales in to account, we have 10 in stock of each item.

I want the inventory to automatically update to show that we only have 7 in stock on the inventory page for each item (given we sold 3 of each from 2 sales) - any help please? We do have 3 digit product codes for our products if it helps to add a column in with numbers instead.

Best,
John

2. ## Re: Excel Stock Inventory/Sales Workbook

in Inventory sheet

D5
Formula:
`Please Login or Register  to view this content.`

E5
Formula:
`Please Login or Register  to view this content.`

Excel 2016 (Windows) 64 bit
B
C
D
E
4
Product In stock quantity Sale Qty In Hand Stock
5
Tennis balls
10
3
7
6
Footballs
10
3
7
7
Rugby balls
10
3
7
 Sheet: Inventory

3. ## Re: Excel Stock Inventory/Sales Workbook

Okay, that works plugging it in to the function, works great. However, I need to understand how this works (as my real inventory isn't as simple as this)...I've searched up the various parts of the function, and while I understand some of the reasons why you use the different parts, still pretty confusing

What does the LEN and the MID mean and why do we use it?

4. ## Re: Excel Stock Inventory/Sales Workbook

Len and mid is used to create criteria without "s". Teninis balls this is reason behind it. in your sale sheet products are with s .

Hope this should help ..

5. ## Re: Excel Stock Inventory/Sales Workbook

I understand this, unfortunately not being able to transform this function in to my actual sales spreadsheet with any function.

6. ## Re: Excel Stock Inventory/Sales Workbook

You upload your exact file after remove confidential information so that we can understand which kind of formula you will require.

7. ## Re: Excel Stock Inventory/Sales Workbook

This is the exact document with confidential info taken out.

Instead of product name I am trying to use 3 digit product SKU to control inventory

8. ## Re: Excel Stock Inventory/Sales Workbook

You selected format for QTY is text change it into General. Follow the steps to do so, select the column I in sheet sales the press control+****+~. once all the qty convert in to number formula would be work.

F7
Formula:
`Please Login or Register  to view this content.`

Drag down
 Row\Col B C D E F G H 3 Inventory Accurate as of: 25/09/2017 4 5 6 Product Product SKU Quantity On Hand Cost Sold Pre-selling quantity 7 x 012 11 1 12.00 1 8 y 018 0 9 z 005 0 10 a 014 0 11 b 036 0 12 c 003 1 13 d 021 0 14 e 022 0 15 f 031 1 16 g 033 8 17

9. ## Re: Excel Stock Inventory/Sales Workbook

The numbers in column I (sales tab) are in text format. You need to convert them into numbers first before applying the SUMIF/S.

Try the following in F7 (Inventory Tab):

=SUMIFS(Sales!P:P,Sales!H:H,C7)

I've converted the numbers stored as text in column I into actual numbers in column P.

See the attached file.

10. ## Re: Excel Stock Inventory/Sales Workbook

Thanks cbatrody - works great now! Didn't realise it was something so simple, had tried many variations but glad to get there! THanks again!

There are currently 1 users browsing this thread. (0 members and 1 guests)