WPS Spreadsheet: Fix SUMIFS Returning One Match for Dates in a Dynamic Array
Question details
The user needs to make SUMIFS include all records that match a date, including dates with hidden fractional time values, and ensure the values being summed are numeric.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A SUMIFS formula is used with dates in a dynamic array or date-based criteria, but the result appears to include only one matching record.
- Observed behavior
- Formatting the dates as short dates does not remove their underlying time values, and amounts stored as text may be excluded from the sum even when the dates match.
Select a few cells in the date column and inspect their values in the formula bar, then check whether the amount column contains real numbers. Use a blank cell with =MOD(date_cell,1) to reveal any fractional time component.
Use a full-day date range in SUMIFS
Use this solution when date cells may contain times such as 12:30 PM or 6:00 PM and the criteria should match the entire calendar day.
A date with a time is stored as a serial number containing a whole-day value plus a fractional day. Instead of comparing the date to one exact serial number, set a lower boundary at the target date and an exclusive upper boundary at the next day.
In the target result cell, enter a formula such as =SUMIFS(amount_range,date_range,">="&target_date,date_range,"<"&target_date+1), replacing amount_range, date_range, and target_date with the relevant cells or ranges.
Select the formula cell, use the fill handle or Copy and Paste from the Home tab, and apply the formula to the remaining target dates in the dynamic list.
In a blank cell, enter =COUNTIFS(date_range,">="&target_date,date_range,"<"&target_date+1) to confirm how many records fall within the complete calendar day.
Normalize dates by removing the time component
Use this approach when you control the worksheet layout and want a clean date-only helper column for repeated calculations.
Convert text amounts into real numbers
Use this diagnostic and correction when COUNTIFS finds the expected records but SUMIFS still returns too little or only one amount.
Resolve Date-Based SUMIFS Errors in WPS Spreadsheet
WPS Spreadsheet lets you inspect underlying date serials, normalize dates, convert text amounts, and build SUMIFS formulas with complete-day criteria so every valid record is included.
- 1. Open the worksheet: Launch WPS Spreadsheet and open the workbook containing the SUMIFS formula from the Home screen or File > Open.
- 2. Inspect the source values: Select cells in the date and amount columns, then use the formula bar to check for time components or text-formatted numbers.
- 3. Apply complete-day criteria: Edit the SUMIFS formula in the formula bar to use >= target_date and < target_date+1 criteria for the date range.
- 4. Verify the result: Use COUNTIFS in a blank cell to count matching records and compare that count with the records included in the corrected sum.
Frequently Asked Questions
Why does SUMIFS match only one record when the dates look identical?
The cells may contain different hidden times even though they display the same short date. Use criteria such as ">="&target_date and "<"&target_date+1 to include the entire day.
Does changing a date to Short Date remove its time value?
No. Formatting changes only the display. To remove the time component, create a helper value with =INT(date_cell), or use a complete-day date range in SUMIFS.
How can I confirm how many records match a date?
Use =COUNTIFS(date_range,">="&target_date,date_range,"<"&target_date+1) in a blank cell. This counts every record occurring on the target calendar day.
Why does COUNTIFS find the rows but SUMIFS does not add all the amounts?
The amount cells may be stored as text. Test the range with =SUM(amount_range), then convert the values using Data > Text to Columns > Finish or a helper formula such as =VALUE(amount_cell).
Can I use a normalized date column for repeated SUMIFS calculations?
Yes. Enter =INT(date_cell) in a helper column, fill it down, and use that date-only column as the SUMIFS criteria range.
