Custom Formatted Cells Not Adding Correctly

[Disarmonious]

I have a row of cells (separated by one blank cell each) that are custom formatted to '00\:00'. Cell I279 is the "sum" cell that sums/adds these cells. This cell's format is '[m]:ss' and its formula is =SUM(TEXT(I273+I275+I277,"00\:00")/60). Maybe someone can explain why the "sum" cell flakes out, and returns an error "#VALUE!" after I've entered "16513" (165:13) in cell I273, "15323" (153:23) in cell I275, and then "20750" (207:50) in cell I277!???

This seems to be something to do with the actual sequence of numbers entered? This doesn't have a problem if I type the numbers "10000" for 100:00 into this cell, but when I type what I need, it's not working! Please help me better understand what's going on here, thank you.

 

[Joe4]

The problem is how you are using SUM in conjunction with TEXT.
The TEXT function does exactly what it sounds like - converts values to text. You cannot sum up text values, only numeric ones.

If you are manually adding the values in your formula like I273+I275+I277, then the SUM function serves no purpose, so get rid of it.
You would use SUM to sum a range of cells, like: =SUM(I1:I100)
If you are physically adding them with a "+", you do not need the SUM function. You only need one or other, not both.

And you do not want to apply the TEXT function until after you have completed your mathematical functions, since you cannot do math functions on text.
So I think you want something like:

[COLOR=#333333]=TEXT((I273+I275+I277)/60,"00\:00")[/COLOR]

 

[Disarmonious]

Joe4,

Thank you for your help. This works perfectly!

 

[Joe4]

You are welcome.