How to Return Values by Date and Header Using XLOOKUP and SUMIFS in Excel
Question details
The user needs to retrieve data for a selected report and date from a table without relying on overly complex nested XLOOKUP formulas.
- Product
- Microsoft Excel (Web / Microsoft 365)
- Device & OS
- not provided
- Scenario
- Extracting and summarizing cross-referenced data from a structured table using a specific date and dynamic column headers as lookup criteria.
- Observed behavior
- The goal is to dynamically return and sum values matching a date row and a specific report header, either via an optimized formula or a PivotTable.
Ensure your source data is formatted as an official Excel Table (e.g., 'Table1') to use structured references, and verify that your date column contains valid date values rather than standard text.
Combine SUMIFS and XLOOKUP for Dynamic Retrieval
Use this method to dynamically look up a column by its header using XLOOKUP, and then use SUMIFS to filter and sum the values based on a specific date.
Instead of nesting multiple lookup functions, you can pass the array returned by XLOOKUP directly into the sum_range argument of a SUMIFS function. This creates a highly dynamic and readable formula.
Click on the cell where you want the result to appear (for example, a summary dashboard cell).
Write the XLOOKUP portion to dynamically find the correct report column. Use XLOOKUP([@Report], Table1[#Headers], Table1). This searches for the report name in the table headers and returns the entire matching data column.
Enclose the XLOOKUP function within SUMIFS to filter the returned column by date. The complete formula should look like: =SUMIFS(XLOOKUP([@Report], Table1[#Headers], Table1), Table1[Date], B$1) where B$1 contains your target date.
Hit Enter. The SUMIFS function is not strictly type-specific, meaning a text string that looks like a valid date in B$1 will often be correctly interpreted and matched against the table's date column.
Use a PivotTable to Summarize and Transpose Data
If you want to avoid complex formulas, a PivotTable provides an interface to group, summarize, and transpose large sets of data based on dates and categories.
Use Advanced Formulas Seamlessly in WPS Spreadsheet
WPS Office Spreadsheet fully supports dynamic array functions, including XLOOKUP and SUMIFS. You can build, execute, and troubleshoot complex data retrieval tasks in a familiar interface without paying for expensive subscriptions.
- 1. Open your Spreadsheet in WPS Office: Launch WPS Spreadsheet and open your existing .xlsx file containing the source table.
- 2. Enter the SUMIFS and XLOOKUP Formula: Select the target cell and type =SUMIFS(XLOOKUP([@Report],Table1[#Headers],Table1),Table1[Date],B$1).
- 3. Calculate and Fill: Press Enter to generate the result. You can then drag the fill handle to apply the dynamic array lookup across multiple rows or columns instantly.
Frequently Asked Questions
Why does my SUMIFS formula return 0 when filtering by date?
This commonly occurs because the date criterion (e.g., in cell B$1) is formatted as text, while the source column contains actual date serial numbers, or vice versa. Ensure both the criteria cell and the table's date column share the exact same date formatting.
Can I use nested XLOOKUPs instead of combining it with SUMIFS?
Yes, a two-way XLOOKUP (nesting one inside another) can find the exact intersection of a row (Date) and a column (Header). However, SUMIFS is highly recommended if you have duplicate dates in your dataset and need to sum all matching values, rather than just returning the first match.
Is XLOOKUP available in older versions of Excel?
No, XLOOKUP is only available in Microsoft 365, Excel for the Web, and Excel 2021 or newer. If you are using Excel 2019 or earlier, you will need to use a combination of INDEX and MATCH functions to retrieve the column dynamically.
