Creating a Dynamic Inventory List Based on Daily Sales

 

Problem:

Columns A & B contain a log of all the sales from a particular day.
Each row consists of the name of an item and the quantity sold.
Columns D & E show the initial inventory for all items.
We want to create formulas in column F that will calculate the current inventory of each item. The formulas should update upon every sale added to the log.

Solution:

Use the SUMIF function in the following formula:
=E2-SUMIF($A:$A,D2,$B:$B)



One thought on “Creating a Dynamic Inventory List Based on Daily Sales

  1. “Hi Scott,

    Easiest way to do this is probably to apply an (auto) filter to your data range, and find the records that need to be adjusted that way.

    You should be able to use the same kind of criteria as you do with the pivot table.

    You might find you have to move beyond an Auto filter, but the full filtering facility should be able to help I would think.

    Alan.”

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>