Summing a row of drop down lists VALUE error

Occasional Visitor

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!

1 Reply

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
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies