Counting Inventory Quantity formula help.

Hello there,

I found this Inventory and Sales Spreadsheet online and I am wanting to be able to add the option to put Quantity column in each month. Currently it only calculates each row as on item sold (when sales column reads 'yes'), however when I sell large quantities to retailers etc, I don't want to have to list them one at a time.

How might I edit the formula in column H under 'Mater Inventory' to reflect the quantity sold? I want that to lookup and calc the quantity from the 'Quantity' column I added to each month.

Please let me know if you need more information or further description.

Thanks in advance,
Lorne

Hello there,

Unfortunately I don't think anything has changed? When I put the quantity of 6 in January, the begining inventory only drops to 6 (from 7) when really it should drop to 1. It's to calculating the quanitity number and only calculating each line in the months as one item?

This formula from H2, is only calculating the line items and not the quantity enter
=COUNTIFS(January[Item],B2,January[Sales],"=Yes")+COUNTIFS(February[Item],B2,February[Sales],"=Yes")+COUNTIFS(March[Item],B2,March[Sales],"=Yes")+COUNTIFS(April[Item],B2,April[Sales],"=Yes")+COUNTIFS(May[Item],B2,May[Sales],"=Yes")+COUNTIFS(June[Item],B2,June[Sales],"=Yes")+COUNTIFS(July[Item],B2,July[Sales],"=Yes")+COUNTIFS(August[Item],B2,August[Sales],"=Yes")+COUNTIFS(September[Item],B2,September[Sales],"=Yes")+COUNTIFS(October[Item],B2,October[Sales],"=Yes")+COUNTIFS(November[Item],B2,November[Sales],"=Yes")+COUNTIFS(December[Item],B2,December[Sales],"=Yes")

Any thoughts to incorporate column K from each individual month?

Thanks,
Lorne

YES! That worked. Thank you!

One final note... you can replace the "B2" in the formula with "[@Item]" (without the quotes, of course)

Hello there,

Sorry to bring up a post from the dead, but it has to do with the same question as before. I want to add one more thing to the formulas you guys helped me out with before.

On the first tab: ORDERS, I have added a column 'QTY' that shows the number of shirts that person ordered. So if you ordered 3 of the same size adn color, rather than 3 separate line items I just throw a 3 in there and move on. However with the example attached, Joe Bob has 2 and Sally Sue has 2. My QTY total is 33. But if you go to the "QTYs" tab it shows 31 because it doesn't take the QTY column from the first tab into account?

How can I take this into account so it sorts and tallies the shirts the same?

Thanks,
Lorne

Use SUMIF with a sum_range. See Excel help on the SUMIF function for details.

Here's how I'd do it...

Hey jhren,

Thanks for the post, however you have a circular reference on the QTYs tab? How can I fix this?

Otherwise it looks like it's working great.

Thanks,
Lorne

Sorry about that...

IMO the easiest way to do what you are looking for is to use a Pivot Table.

The downside is it does not automatically update on changes (though it does have a setting to refresh data upon opening the file).... but a manual refresh isn't all that hard. Just right click anywhere in the PT and choose Refresh. There are also VBA option to automatically refresh, and I've used some... but from what I've experienced, on every VBA refresh you lose your Undo/Redo buffer. Perhaps someone know otherwise or how to get around that.

If a Pivot Table will not suit your needs, we can shoot for another solution, such as resorting closer to your original posted file and simply use SUMIF without changing your helper formulas.

Thanks for the response. I'll play with it and see which direction I want to go. I'm not familiar with Pivot Tables, but seems like agood tool to have under my belt

I hope to take this simple data and expand it into monthly tabs that I can input my sales, losses, and inventory additions and have a master sheet that updates me on inventory currently in stock and another tab that also I can select a retail store from the dropdown and it will update the inventories I have in that specific store.

If you guys are interested in helping with that I can post the file, but it's a big task so I tried to break it down into one question for sanity sake

Thanks again,
Lorne