Inventory management formula required http://www.excelforum.com/showthread.php?t=1130755&p

Hi Everyone!

I am trying, with no success to make a formula like this: =SUMIFS(Sales!J:J,Sales!D:D,"=C16",Sales!B:B,"=S1") work.

I have an inventory spreadsheet for a retail store. On one sheet I input daily sales which is like this: Date SKU(barcode #), product name, qty (per sale so qty is usually 1 or 2)

On another sheet I have inventory like this: SKU (barcode #), opening amount, stock on hand.

I am trying to insert a formula in the stock on hand column that will add all sales of the relevant SKU from the sales sheet. I then want to deduct this total from the opening amount to get a current stock on hand amount. This will allow us to not only see what products are low in stock but also to put stock turn measures around the products to streamline the ordering process.

The problem i'm having is that I can't seem to find an excel formula that will work... but i'm sure there must be a solution!

We have around 500 product SKUs to track so I need to be able to drag the formula down to calculate each one.

Can anyone help???

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

You were almost there.

1. remove the = and "" from the cell ref, otherwise excel is looking for a cell that contains "=A2"
2. you had the column refs swapped around...
=SUMIF(Sales!C:C,A2,Sales!D:D)
sumif(criteria-range,criteria,sum-range)

SumProduct way

Please Login or Register  to view this content.