The cells in the separate worksheets may contain a blank space, as if
someone typed just the space bar and pressed return. That cell would
look blank, visually, but Excel would evaluate it as a text entry and
return the VALUE error because Excel doesn't perform math on a text
string (without some extra manipulation).

Inspect the "foreign" cells and if they appear to be blank or SHOULD be
blank, land the cell pointer on that cell and press the Delete key.
That will remove any entry in that cell and allow Excel to perform math
on a blank cell, which it will do.