Where "In" is the Excel Table on the left, "Out" is the table in the middle.
How this formula works
This formula demonstrates a very simple inventory concept where current inventory is simply the result of all incoming stock minus all outgoing stock. In the example, colors are treated as unique item identifiers – imagine a product available in one size only in just three colors: red, blue, or green.
The key to this approach is to use Excel Tables, because Table ranges automatically expand to handle changes in data. This means we can get a total of all incoming red items with:
If you need to subtotal numbers by color, you can easily do so with the SUMIF function. In the example shown, the formula in G5 is: = SUMIF ( $B$4:$B$11 , F5 , $D$4:$D$11 ) How this formula works The SUMIF function takes three arguments: range,...
To conditionally sum numeric data in an Excel table, you can use SUMIFS with structured references for both sum and criteria ranges. In the example shown, the formula in I5 is: = SUMIFS ( Table1 [ Total ], Table1 [ Item ], H5 ) Where Table1 is an...
SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when adjacent cells meet criteria based on dates, numbers, and text. SUMIFS supports logical operators (>,...
Because tables support structured references, you can learn a lot about a table with simple formulas. In this video, we'll look at some formulas you can use to query a table.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.