Where "In" is the Excel Table on the left, "Out" is the table in the middle.
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:
The SUMIF function takes three arguments: range, criteria, and sum_range. In this case, we are using: Range: $B$4:$B$11 - This is the set of cells to which the criteria (a color from column F in this case) will be applied. This is an absolute...
This formula uses structured references to feed table ranges into the SUMIFS function. The sum range is provided as Table1[Total] , the criteria range is provided as Table1[Item] , and criteria comes from values in column I. The formula in I5 is: =...
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.