Function Problems

Excel Formula to Return Daily Values from a Dynamic Date Range

Nimra Malik Oct 21, 2026 868 views

Question details

The user needs to convert a static SUMPRODUCT formula into a dynamic array formula that returns individual daily values from a specified date range, sorted by driver seniority.

How to Return Daily Values from a Dynamic Date Range Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Transitioning from a single total of the previous eight days to extracting the underlying daily values into a table, automatically sorted by a specific driver seniority column.
Observed behavior: The user requires an array output (spilled results) rather than a single aggregated sum or a chart, correctly mapped to driver ranges and active dates.

Before you start

Ensure you are using a version of Excel or WPS Office that supports modern dynamic array functions (like FILTER and SORTBY), and verify there is enough empty space below your destination cell to accommodate the spilled results.

Solution 1Recommended

Use FILTER and SORTBY Functions for Dynamic Extraction

Combine the FILTER function to extract records matching your date criteria and the SORTBY function to order the resulting array by driver seniority.

Dynamic array formulas allow you to write a single formula that returns values into multiple cells automatically. By wrapping a FILTER function inside a SORTBY function, you can dynamically extract the previous 8 days of data and present it in a sorted table without manually copying formulas down the column.

Identify your data ranges

Locate your source table ranges. Note the columns containing the Daily Values, the Dates, and the Driver Seniority metrics. Ensure they all cover the same exact row numbers (e.g., A2:A100, B2:B100).

Construct the FILTER function

Select the top-left cell of your destination area. Type the formula =FILTER(Value_Range, (Date_Range>=TODAY()-8)*(Date_Range<=TODAY())) to pull the daily values from the last eight days.

Apply the SORTBY function

Modify the formula to wrap the FILTER function with SORTBY. Update it to: =SORTBY(FILTER(Value_Range, (Date_Range>=TODAY()-8)*(Date_Range<=TODAY())), Seniority_Range, 1). The '1' indicates ascending order.

Trigger the dynamic spill

Press Enter to apply the formula. The data will automatically spill down and populate the adjacent cells, creating a dynamically updated table of daily totals sorted by seniority.

Use FILTER and SORTBY Functions for Dynamic Extraction

Handling Range Dimensions: Make sure the arrays for Values, Dates, and Seniority all possess the identical number of rows. Mismatched array sizes will result in a #VALUE! error.

Advanced Spreadsheet Tool

Easily Manage Dynamic Arrays in WPS Spreadsheet

WPS Office fully supports advanced dynamic array formulas, allowing you to seamlessly process functions like FILTER and SORTBY. Create comprehensive reports, filter date ranges, sort data, and export your work effortlessly for free.

1. Open your dataset: Launch WPS Spreadsheet and load your .xlsx workbook containing dates, daily values, and driver seniority details.
2. Select the output area: Click on the top-left cell of the blank area where you want your new dynamic table to appear.
3. Enter the formula: Type your =SORTBY(FILTER(...), ...) formula and press Enter to instantly spill the required data into the rows below.
4. Save and share: Click 'Save' to retain your document in the standard .xlsx format, ensuring perfect compatibility if shared with Microsoft Excel users.

Full format compatibility with Microsoft Excel files (.xlsx) and formulas.Supports modern dynamic array functions including FILTER, SORTBY, and UNIQUE without lagging.Lightweight, fast-loading, and completely free alternative for managing complex data sets.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #SPILL! error when using the FILTER function?

A #SPILL! error occurs when the dynamic array formula needs to populate multiple cells, but one or more of the destination cells already contain text, spaces, or formatting. Clear the cells below and to the right of the formula to allow the results to display properly.

Can I use multiple date criteria within the FILTER function?

Yes, you can multiply conditions together to achieve 'AND' logic. For example, using (A2:A50>=DATE(2023,10,1))*(A2:A50<=DATE(2023,10,31)) inside the FILTER criteria argument will return values that only fall within October 2023.

How do I dynamically reference the last 8 days in my formula?

You can use the TODAY() function within your criteria. For instance, replacing specific dates with (Date_Range<=TODAY())*(Date_Range>=TODAY()-8) will continuously evaluate the rolling eight days based on your computer's current date.

Excel & Data Problems