SOLVED

Nested IF formula column’s SUM problem

Occasional Contributor

5DD53C79-C82E-404F-BAE3-694F70A971B9.jpeg

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

@DEOL48 

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

@DEOL48 

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)
Solution

@DEOL48 

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 . 

A16552A2-2F10-4B09-B239-5B052C7FAA87.jpeg

@DEOL48 

Please apply elapsed time format

image.png

It’s worked. Thanks

@DEOL48 , you are welcome