Inventory Management Using SUMIF function

User Question on inventory management using a simple method: I deal in 150 types of items in a shop. For example if I know I have 70 pieces of an item in stock and I take out 20 and add 30, I wish to have the recalculated inventory quickly. I wish to use a single column for incoming and outgoing stock and wish to track the daily changes by date. I want a very simple system that gives me a base count and a method to show what is going out and what is coming in and a current total. We can setup Microsoft Excel to perform this task as follows: We enter the date, item and purchase or sale quantity The sale and purchase or buy quantities are enterd in the same column Purchase entries are positive. Sales entries are negative. We format the sales entries in such a way that the data is red colored without the negative sign We leave a column or two and enter our items in one column In the next column we use the sumif function to find out the current stock quantity In this manner we have our daily sales and purchase and our current stock. Watch the video below to see how you can implement a simple straightforward inventory management system using the sumif function and no macros:

Download Excel File

Download Excel File for versions Excel 97-2003

Leave a Reply

Your email address will not be published. Required fields are marked *