Nested IF formula column’s SUM problem

Occasional Contributor


Hi there , I have a column with Nested IF formula and I need to sum of column and sum is showing 00:00 with SUM formula. Can anyone solve this error 

8 Replies


The values in column F are left-aligned. That suggests they might be text values.

Try the following:

  • Select column F.
  • Set the number format to a time format,
  • On the Data tab of the ribbon, click Text to Columns.
  • Click Finish.

Does that make a difference?

There is also formula to get answer of time spend on work . Formula is =MOD(D251-C251,1). IN ‘G’ column have a nested formula IF that’s show in pic , but I need a sum of G column which is last column on right


Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

best response confirmed by DEOL48 (Occasional Contributor)


You use "10:00" in your formula. That is text. And SUM() ignores text.

Either convert with TIMEVALUE() or double-negative or use TIME() or 10/24.

@Detlef Lewin yes, this has been worked , but SUM still not working if I select them sum shows on bottom . 



Please apply elapsed time format


It’s worked. Thanks

@DEOL48 , you are welcome