You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Using sum formulas with blank cells

Hello.


Long story short, I'm summing some specific cells which are empty, but formatted as a duration (0:00 meaning 0 hours 0 minutes). I need to get 0:00 as a result, but instead I'm getting 0.


This is problematic for me, because in the same spreadsheet I'm summing some duration times that actually contain some data with mentioned zeroes. As a result, there's an error in the cell because of cell format.


Any ideas how to solve the problem without filling blank cells with "0"?


MacBook Air 13″, macOS 15.0

Posted on Oct 7, 2024 3:42 PM

Reply
1 reply

Oct 7, 2024 5:35 PM in response to VavelDragon

You must have all durations or all numbers in the range that you are trying to sum. Blank cells are acceptable and will just be ignored.




If you are creating the these values via formula then make sure all the formulas give you durations or all give you numbers.


Note that you can convert from number to duration using the DURATION function.


DURATION - Apple Support


You can convert from duration to numbers using the DUR2 family of functions.


DUR2HOURS - Apple Support


SG



Using sum formulas with blank cells

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.