Sometimes we face the problem of the sum of time not working in Excel. In this article, we will show you how to solve the problem of the sum of time not working in Excel with 5 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.
📂 Download Excel File
Download free workbook from here:
Learn How to Solve When Sum of Time Is Not Working in Excel with These 5 Methods
While writing this article, “5 Easy Ways to Fix When SUM Time Is Not Working in Excel”, we’ve considered a dataset that contains approximately 5 columns and 12 rows. In this dataset, we added Name, Working Hours (Day 1), Working Hours (Day 2), and Outcome to make you better understand. But if you want, you can change the entities and put your own values.
1. Applying SUM Function When Sum of Time Not Working
You can apply the SUM function to the sum of time not working in Excel. It is effortless and simple. You can do this with ease if you maintain the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell E6.
- Secondly, write the following function on that cell.
=SUM(C6:D6)
- Now press Enter.
- Thirdly, use the fill handle icon to drag the formula for the entire column.
- Finally, you will get the desired result.
Undoubtedly, this is a very handy technique for the sum of time not working in Excel. You can also use this method to sum time in Excel.
📕 Read More: 5 Ways to Calculate Total Hours Worked in a Week in Excel
2. Obtaining Sum of Time in Separate Cells
There is another way to the sum of time not working in Excel that we can use very easily. You just need to follow the below-given steps to achieve this process.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, you need to select cell E6.
- Secondly, write down the following function on that cell.
=C6+D6
- Tap Enter.
- Thirdly, you need to use the fill handle icon to drag the formula for the entire column.
- Eventually, you will get the required result.
Truly, this approach is very useful to work on.
📕 Read More: 4 Examples of Timesheet Formula with Lunch Break in Excel
3. Adopting AutoSum Feature
Adopting the AutoSum feature is one of the simplest ways to solve the sum of time not working in Excel. You just have to follow the given steps here.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell E6.
- Secondly, go to the Home tab.
- Thirdly, click on the Editing option.
- Then tap on AutoSum.
- After that, you will see a picture like below.
- Fourthly, press Enter.
- Belatedly, you just use the fill handle icon to drag the formula for the entire column.
- Consequently, you will get the required result.
Truly, this is a very handy technique to solve the sum of time not working in Excel.
📕 Read More: 7 Examples to Calculate Duration of Time in Excel
4. Changing Cell Format to Sum Time Over 24 Hours in Excel
If you want to solve the sum of time not working in Excel then changing the cell format is a very handy procedure to achieve. You will have to follow the steps given below.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, you need to select cell C12.
- Secondly, put the following formula on that cell.
=SUM(C6:C11)
- Now press Enter.
- You will get a value after pressing Enter. But this value is not our required value. So, we will perform the further operation.
- Press Ctrl+1.
- You will get a dialogue box name Format Cells.
- Thirdly, select Number.
- Then select Custom from the Category.
- Now select Type and write-
[h]:mm:ss
- After that click on the OK button.
- Now you will find the result that you wanted.
- Belatedly, use the fill handle icon to drag the formula for the entire column.
- Eventually, you will get the required result.
Finally, we can say that this is a very easy way to obtain.
📕 Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
5. Implementing Multiplication Operation
Implementing multiplication operation to solve the sum of time not working in Excel is one of the basic techniques. If you follow the below steps, I am sure that you can do it easily.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, you need to select cell C12.
- Secondly, write the following formula on that cell.
=SUM(C6:C11)
- Thirdly, press Enter.
- You will get a value after tapping Enter. But this value is not our desired value. So, we will perform another operation.
- Again, you need to select cell C12.
- Then you need to write the following formula on that cell.
=SUM(C6:C11)*24
- Afterward, tap Enter.
- Now the result looks like our wanted result.
- Fifthly, you have to use the fill handle icon to drag the formula for the entire column.
- Finally, you will get the required result.
Truly, this is a very handy technique to solve the sum of time not working in Excel.
📕 Read More: 6 Approaches to Subtract Time in Excel and Show Negative
What to Do When Sum of Time Returns 0 in Excel
Sometimes you may face a problem with the sum of time returning 0 in Excel. This problem occurs when you give the input of time as text.
To solve this problem, you need to maintain the following steps given below which contain the SUMPRODUCT function.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, you have to select cell E6.
- Secondly, write the following function on that cell.
=SUMPRODUCT(–C6:D6)
- Then press Enter.
- Thirdly, you must use the fill handle icon to drag the formula for the entire column.
- Finally, you will get the desired result which does not contain 0.
Indeed, this approach is very useful to solve the problem of the sum of time returning 0 in Excel.
📄 Important Notes
You should be aware of the following things while performing the processes mentioned above:
🖊️ You have to be very careful while using the SUM function.
🖊️ You need to be careful while changing the cell format.
📝 Takeaways from This Article
You should be aware of the following things while performing the processes mentioned above:
📌 Firstly, we have used the SUM function to solve the sum of time not working in Excel.
📌 Secondly, we obtained the sum of the time in separate cells.
📌 Belatedly, we adopted the AutoSum feature in Excel.
📌 Afterward, we changed the cell format.
📌 Fifthly, we tried to implement multiplication operations.
📌 Finally, we tried to solve the problem of the sum of time returning 0 in Excel.
Conclusion
We wish that you would be able to solve the problem of the sum of time not working in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.
Related Articles
- 4 Easy Ways to Calculate Percentage of Time in Excel
- How to Calculate Average Response Time in Excel
- 9 Tricks to Calculate Time Difference Between AM and PM in Excel
- 4 ways to Create Excel Formula for Overtime Over 8 Hours
- 2 Ways to Calculate Travel Time Between Two Cities in Excel