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)

Users are saying about us...

  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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube