Summing a row of drop down lists VALUE error

[alexgowen]

Hello all, 

I am trying to sum a row of cells where each cell has a drop down list created through Data Validation using Excel 2010. Each list has 2 options, a blank cell or a time that is formatted in the custom format h" hrs "m" mins". 

For some reason when another user chooses the options from the drop down list, the formula that sums the cells presents a #VALUE error. The summing formula is =TEXT(L14+M14+N14+O14+P14,"hh:mm")

and I have attached the sheet to this message. 

I'm thoroughly stumped as to why when others view and select from the drop down lists, the answer that is given is no longer recognised by the formula. The perfect solution would be that the formula gives out a time in either hh:mm or the above custom format, but any advice is much appreciated!

[#2]

Alex,

Why use =TEXT to add the times as all your values are in time format anyway so a simply adding the cells will give the same answer.
check https://support.office.com/en-us/article/how-to-correct-a-value-error-in-average-or-sum-functions-3e... for correcting value errors