digest
Short Summary
Main recommendation: Use XLOOKUP inside SUMIFS to find the correct column for each row when the condition values sit in the column headers, rather than adding helper logic. XLOOKUP can return a range object that SUMIFS accepts, so the sum updates automatically for each row's state. If XLOOKUP is unavailable, INDEX and MATCH can achieve the same result.
Key Points
- SUMIFS is the most efficient function for adding with multiple conditions, but its criteria must be in a range or a range object.
- When the state condition is in the column headers, XLOOKUP can look up that state across multiple columns, and the lookup array and return array must have the same number of columns.
- XLOOKUP spills the entire looked-up column if used alone; placing it inside SUMIFS avoids that and passes a single range.
- The same approach works in a two-way lookup because the formula adjusts the column it looks up as it moves down the rows (for example, California rows versus Washington rows).
- Without XLOOKUP, INDEX and MATCH can return a full column range that SUMIFS accepts.
Details Worth Noting
- Concrete instructions:
- Use XLOOKUP inside SUMIFS for the sum range, with the state as the lookup value, the headers row as the lookup array, and the matching data block as the return array.
- Press F4 to lock just the lookup array reference; exact match is used by default (the return array is not described as locked with F4).
- Lock the criteria range columns (for example, the entire product column F with F4) and set criteria one to the row's product and criteria two to the row's sales rep.
- Fill the formula down with Control-Enter and double-click to fill.
- To check, click the state cell in the Washington row and press F9 to confirm the correct column is being looked up; press Escape to exit.
- Without XLOOKUP, use INDEX with the row number left empty or set to zero (to return all rows), and MATCH for the correct column position, then place that range directly into SUMIFS.
- XLOOKUP was only available in Office 365 Insider at the time of recording (September 13, no year given); Microsoft said it would come to all of Office 365 later.
- Older approaches used SUMPRODUCT as an array operation combined with INDEX and MATCH before SUMIFS-style multi-condition summing was available.
- The speaker points to separate videos for a comprehensive XLOOKUP introduction and a three-way lookup example.
Bottom Line
When lookup values sit in headers and sums depend on multiple conditions, nesting XLOOKUP or INDEX-MATCH inside SUMIFS produces a clean, fill-down-friendly solution.
