[Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)

Microsoft Excel is one of the most popular applications used in big corporates and business centers. It contains lots of built-in functions that help us to perform different tasks needed for our day-to-day works. One of them is to find the sum of time values in Excel. In this article, we will discuss the solutions when SUM is not working with time values properly in Excel.


Download Practice Workbook

Download the following workbook to exercise while you are reading this article.


5 Solutions to ‘SUM Not Working’ Issue with Time Values in Excel

We have taken the data set of one employee for two weeks. Now, we will sum the time value and show solutions to the problem.

We can sum time in two ways in Excel. One problem occurs when we sum time. That is, we get the remainder of the time instead of getting the actual sum of time. If the sum of time is less than 24 hours no problems at all. But when the sum exceeds 24 hours, we get the reminder value only. So, the solution of the mentioned problem will be shown with the sum of time in the following methods.


1. Get the SUM of Time Value Inserting Individual Cells

Here, we will sum the time values by inserting the cell reference individually. For this, we add a column named Result.

Step 1:

  • Now, go to Cell E5.
  • Find the sum of the Cells C5 and D5 using the following formula –
=C5+D5

SUM of Time Value Inserting Individual Cells

Step 2:

  • Press the Enter.

We get the sum of the working time on Monday of Week 1 and Week 2.

Step 3:

  • Now, pull the Fill Handle icon.

SUM of Time Value Inserting Individual Cells

Here, we get the sum of time. We can add more than two values in the same way.

Related Content: How to Calculate Total Hours in Excel (9 Easy Methods)


2. Apply SUM Function to Find Time Value

Here, we will use the SUM function to sum time values.

Step 1:

  • Go to Cell E5.
  • Write the following formula consisting of SUM function –
=SUM(C5:D5)

Apply SUM Function to Find Time Value

Step 2:

  • Now, press Enter.

Step 3:

  • Pull down the Fill Handle icon to the last cell.

Apply SUM Function to Find Time Value

Here, we get the sum of two weeks based on each day.


3. Sum Time Applying the AutoSum Tool

We will apply the AutoSum tool here.

Step 1:

  • First, need to select the cell where we need to apply the AutoSum tool.
  • Here we select Cell E5.
  • Now, go to the Home tab.
  • From Editing choose the AutoSum tool.

Sum Time Applying the AutoSum Tool

Step 2:

  • When we click AutoSum, a formula will appear on that cell.
  • We can edit the formula argument if need.

Sum Time Applying the AutoSum Tool

Step 3:

  • Then press Enter.

Step 4:

  • In the same way, apply AutoSum in the rest of the cells.

Here, auto sum makes it simple to sum the time values.


Similar Articles:


4. Change the Cell format to Solve Not Working Issue with SUM the Time Value

In this section, we will show how we get the exact sum time when the sum is more than 24 hours.

To show the sum of each week we add a row below the existing data set named Result.

Step 1:

  • Go to Cell C10.
  • Apply the SUM function as shown before.
=SUM(C5:C9)

Cell format to Solve Not Working Issue with SUM the Time Value

Step 2:

  • Then press Enter.

Here, we did not the exact sum value. As the sum exceeds 24 hours it only shows the reminder value. Now, we will show how to solve this.

Step 3:

  • Select Cell C10 cell and press Ctrl+1.
  • We get a window named Format Cells.
  • Select the Custom category in the Number section.
  • Now, select [h]:mm:ss type.

Cell format to Solve Not Working Issue with SUM the Time Value

Step 4:

  • Finally, press OK.

Cell format to Solve Not Working Issue with SUM the Time Value

Here, we get the exact sum of time.

Step 5:

  • In the same way, get the sum at Cell D10.

This is how to get the solution of sum time.


5. Perform Multiplication Operation to When SUM of Time Not Working

Here, we will multiply the sum of time by 24 to get the exact sum.

Step 1:

  • First, apply the following SUM formula at cell C10
=SUM(C5:C9)

Step 2:

  • Then press Enter.

This result is not according to our needs. To get the exact time value we multiply this value by 24 and that will be the value-based hour unit.

Step 3:

  • Modify the formula in the following way –
=SUM(C5:C9)*24

Multiplication Operation to When SUM of Time Not Working

Step 4:

  • Then press Enter.

Step 5:

  • Drag the Fill Handle icon towards the right side.

Multiplication Operation to When SUM of Time Not Working

This is our required value. This value is based on the hour unit.


Conclusion

In this article, we described the sum of time in Excel and showed the solution when the sum of time is not working. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo