Why does my column return #INVALID VALUE in random rows

Admin

I have a training matrix and want to record total hours trained so have added a helper column to the end of each course to record the length of the course and then I have added all columns to produce total hours spent using the following formula (a bit long but only way I could get it to work!). The formula has worked on 90% of rows but some are throwing up an #INVALID ERROR any idea why?

=SUM(VALUE([Column155]@row), VALUE([Column156]@row), VALUE([Column157]@row), VALUE([Column158]@row), VALUE([Column159]@row), VALUE([Column160]@row), VALUE([Column161]@row), VALUE([Column162]@row), VALUE([Column163]@row), VALUE([Column164]@row), VALUE([Column165]@row), VALUE([Column166]@row), VALUE([Column167]@row), VALUE([Column168]@row), VALUE([Column169]@row), VALUE([Column170]@row), VALUE([Column171]@row), VALUE([Column172]@row), VALUE([Column173]@row), VALUE([Column174]@row), VALUE([Column175]@row), VALUE([Column176]@row), VALUE([Column177]@row), VALUE([Column178]@row), VALUE([Column179]@row), VALUE([Column180]@row), VALUE([Column181]@row), VALUE([Column182]@row), VALUE([Column183]@row), VALUE([Column184]@row), VALUE([Column185]@row), VALUE([Column186]@row), VALUE([Column187]@row), VALUE([Column188]@row), VALUE([Column189]@row), VALUE([Column190]@row), VALUE([Column191]@row), VALUE([Column192]@row), VALUE([Column193]@row), VALUE([Column194]@row), VALUE([Column195]@row), VALUE([Column196]@row), VALUE([Column197]@row), VALUE([Column198]@row), VALUE([Column199]@row), VALUE([Column200]@row), VALUE([Column201]@row), VALUE([Column202]@row), VALUE([Column203]@row), VALUE([Column204]@row))

Extract of sheet

Admin

Just to add this is for the current year which is why there are not values in some passed rows.

TVang

It's likely that at least one of those 50 cells for that row contains non-numeric characters. The VALUE() function returns #INVALID VALUE when this happens.

Paul Newcome

In your screenshot, the only populated row in [Column155] is "0:50". Check to make sure you have valid numbers in every cell.

Admin

Some cells are intentionally blank as they have not passed training in 2025 but all the other lines still work?

Paul Newcome

The blanks are not the problem. The cells containing data such as 0:50 are the problems. The colon should actually be a decimal.