Formula Errors

How to Use TODAY in a SUMIFS Date Criteria Formula in Excel & WPS

Maira Mehtab Oct 14, 2026 870 views

Question details

The user needs to dynamically sum values based on a rolling date range (such as the last 30 days) using the TODAY function within a SUMIFS formula without triggering errors.

How to Correctly Use TODAY in a SUMIFS Date Criteria Formula

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating a dynamic date criteria in a SUMIFS formula to total values over a specific recent time period, like the past 30 days.
Observed behavior: Entering the criteria directly as text (e.g., ">TODAY()-30") causes a #NAME? error or returns a zero result because the function is treated as a plain text string and is not evaluated.

Before you start

Ensure that your date column contains valid date formats rather than text strings, and that your target sum range contains numerical data.

Solution 1Recommended

Use Concatenation with the Ampersand (&) Operator

To correctly use functions like TODAY() within SUMIFS criteria, you must separate the comparison operator from the function and join them using an ampersand.

When building dynamic criteria in functions like SUMIFS, COUNTIFS, or AVERAGEIFS, entering the entire expression inside quotation marks (e.g., ">TODAY()-30") treats it as plain text. This prevents the spreadsheet from calculating the actual date, which often results in a #NAME? error or a zero value.

Select the target cell

Click on the cell where you want the calculated sum result to be displayed.

Start the SUMIFS function

Type `=SUMIFS(` and select your sum range followed by your first criteria range (e.g., `=SUMIFS(D1:D16, C1:C16, `).

Enter the concatenated date criteria

Type your comparison operator wrapped in quotes, followed by an ampersand and the TODAY function. For example, to find dates older than 30 days ago, type `">"&TODAY()-30`.

Add the upper limit criteria

To restrict the sum to dates before today, add the criteria range again and type the upper limit: `C1:C16, "<"&TODAY()`.

Complete the formula

Close the parentheses and press Enter. The final formula should look exactly like: `=SUMIFS(D1:D16, C1:C16, ">"&TODAY()-30, C1:C16, "<"&TODAY())`.

Use Concatenation with the Ampersand (&) Operator

Syntax Reminder: All logical operators such as =, <, >, and <> must be placed inside double quotation marks when concatenated with a formula function or a cell reference.

Data Analysis Made Easy

Calculate Dynamic Date Ranges Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like SUMIFS and TODAY(). You can effortlessly calculate dynamic totals, manage financial records, and analyze time-based data using the exact same formula syntax as you would in Microsoft Excel.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your date and value columns.
2. Input the dynamic formula: Select the target cell and enter your SUMIFS formula, ensuring you use the `&` operator to properly join the comparison operators with the TODAY() function.
3. Calculate and apply: Press Enter to instantly get your dynamic sum. You can use the drag-fill handle at the bottom right of the cell to copy this logic across other summary cells.

100% compatible with Microsoft Excel formulas, functions, and file formats.Built-in formula syntax prompts to help you avoid common concatenation errors.Lightweight software with lightning-fast calculation speeds for large datasets.Completely free to use with a familiar, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMIFS formula return 0 when using the TODAY function?

If you wrap the entire expression in quotation marks (like ">TODAY()-30"), the spreadsheet reads it as literal text instead of evaluating the date function. This means it looks for cells containing the exact word 'TODAY', which results in zero matches. Always separate the operator and use the ampersand to concatenate.

Can I use a cell reference instead of the TODAY function for date criteria?

Yes. If your target date is stored in a cell (for example, A1), you can reference it using the exact same concatenation method. The criteria part of your formula would look like `">"&A1`.

Does this ampersand concatenation rule apply to COUNTIFS and AVERAGEIFS?

Yes. All conditional functions in the 'IFS' family, including COUNTIFS, AVERAGEIFS, and MAXIFS, require logical operators to be wrapped in quotes and concatenated with functions or cell references.

Excel & Data Problems