Explanation
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(Table1[Total],Table1[Item],H5)
which resolves to:
=SUMIFS(F5:F89,D5:D89,"Shorts")
The SUMIFS function returns 288, the sum values in the Total column where the value in the Item column is "Shorts".
Related functions
SUMIFS Function
The Excel SUMIFS function returns the sum of cells that meet multiple conditions, referred to as criteria. To define criteria, SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be used with cells that contain dates, numbers, and text.
Related videos
How to use SUMIFS with an Excel Table
In this video, we'll look at how to use the SUMIFS function with an Excel Table. On this worksheet, I have two identical sets of order data. I'm going to walk through the process of constructing a summary of sales by item for both sets of data. With the data on the left, I'll use standard formulas...
Introduction to structured references
In this video, I'll give a brief introduction to structured references. A structured reference is a term for using a table name in a formula instead of a normal cell reference. Structured references are optional, and can be used with formulas both inside or outside an Excel table. Let's take a look...
Formulas to query a table
In this video, we'll look at some formulas you can use to query a table. Because tables support structured references, you can learn a lot about a table with basic formulas. On this sheet, Table1 contains employee data. Let's run through some examples. To start off, you can use the ROWS function to...
