How to Return Current or Uncurrent in WPS Office Excel Using SUMIFS Date and Value Criteria
Question details
The user needs a formula that returns "Current" when a date is within the last 90 days and the total value exceeds 1, and returns "Uncurrent" otherwise.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is working with a large dataset of dates and values and needs to classify rows based on a recent date range and a total value threshold.
- Observed behavior
- The desired result is "Current" when the SUMIFS total for dates since TODAY()-90 is greater than 1, and "Uncurrent" when the condition is not met.
Verify that the date column contains real Excel date values and the value column contains numeric data. Note the exact ranges for the value column, such as M4:M9999, and the date column, such as B4:B9999, so you can adjust the formula quickly.
Use SUMIFS with TODAY() to Return Current or Uncurrent
Use this method when you need a single-cell formula that checks both the last 90 days and the total value threshold without adding helper columns.
SUMIFS adds the values in M4:M9999 where the dates in B4:B9999 are greater than or equal to TODAY()-90. The IF function then returns "Current" if that total is greater than 1, and "Uncurrent" if it is not.
Open your workbook in WPS Office Spreadsheets and select the blank cell where you want the Current or Uncurrent result to appear, such as M1.
Type =IF(SUMIFS(M4:M9999,B4:B9999,">="&TODAY()-90)>1,"Current","Uncurrent") and press Enter.
If your value column is not M4:M9999 or your date column is not B4:B9999, replace those ranges with the correct columns and row extent in your worksheet.
Use WPS Spreadsheets to Return Current or Uncurrent with SUMIFS and TODAY
WPS Office Spreadsheets supports the same SUMIFS, IF, and TODAY functions, so you can enter this formula directly in a cell and get Current or Uncurrent results in a free, lightweight spreadsheet that opens Excel files seamlessly.
- 1. Open WPS Spreadsheets: Download and open WPS Office, then create a new workbook or open your existing Excel file in WPS Spreadsheets.
- 2. Select the result cell: Click the blank cell where you want the Current or Uncurrent result to appear, such as M1.
- 3. Enter the formula: Type =IF(SUMIFS(M4:M9999,B4:B9999,">="&TODAY()-90)>1,"Current","Uncurrent") and press Enter.
- 4. Adjust the ranges: Replace M4:M9999 and B4:B9999 with your actual value and date ranges if they are different from the example.
Frequently Asked Questions
Can I use this formula if my dates are in column A and values are in column C?
Yes. Replace B4:B9999 with your date range and M4:M9999 with your value range, and keep the date criterion as ">="&TODAY()-90.
Why does the formula return Uncurrent even when a date is within 90 days?
The formula requires the total value to be greater than 1. If matching values sum to 1 or less, or if the value cells are not numeric, the formula returns Uncurrent.
How do I exclude future dates from the 90-day calculation?
Add a second date criterion to SUMIFS: =IF(SUMIFS(M4:M9999,B4:B9999,">="&TODAY()-90,B4:B9999,"<="&TODAY())>1,"Current","Uncurrent").
Does this formula work in both WPS Spreadsheets and Excel?
Yes. WPS Spreadsheets supports SUMIFS, TODAY, and IF, and it opens .xlsx files, so the same formula normally works in both applications.
