Fix MAXIFS and SUMIFS Returning Zero for Time Values in WPS Office Excel
Question details
User needs to troubleshoot MAXIFS or SUMIFS formulas that return zero when the source column contains time durations stored as text instead of numeric Excel time values.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is working with a spreadsheet where a source column contains time durations, and MAXIFS or SUMIFS formulas are expected to aggregate those times based on conditions.
- Observed behavior
- MAXIFS or SUMIFS formulas return zero even when matching time values appear in the source column.
Before changing the formula, test a source time cell with =ISTEXT(J2). If the result is TRUE, the time values are stored as text and must be converted to numeric values before MAXIFS or SUMIFS can calculate them.
Convert Text Time Values to Numeric Times with Text to Columns
Use this method when the source column contains time durations stored as text and you want to fix the original range in place.
MAXIFS and SUMIFS ignore text values in numeric criteria ranges, so a text duration can never match a numeric time condition. Converting the range to real numeric time values lets the functions evaluate criteria and sum the corresponding cells.
Select the source column that contains the time durations, for example J2:J100.
In WPS Office Excel, go to the Data tab and click Text to Columns.
In the Convert Text to Columns Wizard, select Delimited, click Next, clear all delimiter checkboxes, and click Next.
Under Column data format, select General, click Finish, and then press Ctrl+1 to open Format Cells.
In Format Cells, choose Custom, enter [h]:mm:ss in the type box, and click OK. Retry the MAXIFS or SUMIFS formula.
Create a Helper Column with the VALUE Function
Use this method when you cannot modify the original column or want a quick formula-based check.
Use Power Query to Convert Text Time Values to Numeric Time
Use this method for large datasets or repeatable cleanup tasks.
Fix MAXIFS and SUMIFS Time Calculations in WPS Office Excel
WPS Office Excel can convert text-based time durations into numeric time values with Text to Columns, apply a custom [h]:mm:ss format, and make MAXIFS or SUMIFS formulas calculate correctly.
- 1. Test a time cell for text: In WPS Office Excel, select a blank cell and type =ISTEXT(J2), then press Enter. TRUE means the duration is stored as text.
- 2. Run Text to Columns: Select the time column, go to Data > Text to Columns, choose Delimited, click Next, clear all delimiters, and click Next.
- 3. Apply a time format: Set the column data format to General, click Finish, then press Ctrl+1, choose Custom, enter [h]:mm:ss, and click OK.
- 4. Verify the formula result: Re-enter or recalculate the MAXIFS or SUMIFS formula to confirm it returns the expected non-zero result.
Frequently Asked Questions
Why does MAXIFS return 0 for time durations that look correct?
The source time values are likely text, not numeric Excel times. Use =ISTEXT(J2) to check; if it returns TRUE, MAXIFS and SUMIFS will not match or sum those cells until they are converted.
How do I convert text time values to numeric time in WPS Office Excel?
Select the column, go to Data > Text to Columns, choose Delimited, clear delimiters, choose General, finish, and then apply Custom format [h]:mm:ss.
Can I keep the original column and still fix the formulas?
Yes. Use a helper column with =VALUE(J2), format the helper cells as [h]:mm:ss, and point MAXIFS or SUMIFS to that helper column.
Will WPS Office work with Excel files that use MAXIFS and SUMIFS?
Yes. WPS Office Excel supports .xlsx workbooks and common Excel functions, so you can open, edit, and save these formulas without reformatting.
What time format should I use for durations longer than 24 hours?
Use the custom number format [h]:mm:ss. The square brackets keep hours from resetting at 24, which is important for summed durations.
