Value Error when copying formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2773434%22%20slang%3D%22en-US%22%3EValue%20Error%20when%20copying%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EHi%20Folks.%26nbsp%3B%20Apologies%20from%20the%20outset%3A%20I'm%20not%20great%20with%20Excel.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EI'm%20using%20an%20simple%20Excel%20spreadsheet%20that%20I%20set%20up%20a%20long%20time%20ago.%26nbsp%3B%20I%20have%20a%20%22Totals%22%20column%20where%20the%20final%20cell%20contains%20a%20formula%20that%20is%20copied%20from%20the%20cell%20above%20it%20which%20in%20turn%20is%20copied%20from%20the%20cell%20above%20that%20one%2C%20etc.%2C%20etc.%26nbsp%3B%20So%20far%20the%20copy%20has%20worked%20over%201500%20times.%26nbsp%3B%20The%20actual%20formula%20reads%3A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3DSUM(S1975%2BP1976-Q1976)%20and%20so%20the%20resulting%20value%20changes%20according%20to%20what%20figures%20are%20entered%20in%20the%20P%20and%20Q%20cells.%20However%20all%20I%20get%20is%20a%20~VALUE!%20message.%26nbsp%3B%20I've%20tried%20entering%20the%20formula%20myself%20rather%20than%20copying%20it%20but%20I%20still%20get%20the%20same%20error%20message.%26nbsp%3B%20I%20can%20see%20no%20mistakes%20in%20the%20figures%20I've%20put%20it%20into%20P%20and%20Q%20cells%20and%20no%20error%20in%20the%20formula%20I'm%20putting%20in%20manually.%26nbsp%3B%20Help%2C%20please!%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2773434%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2773480%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20Error%20when%20copying%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773480%22%20slang%3D%22en-US%22%3EThere%20is%20nothing%20wrong%20with%20your%20formula.%3CBR%20%2F%3EThe%20problem%20is%20with%20the%20values.%20Maybe%20some%20text%20value%20but%20showing%20as%20numbers%2C%20or%20different%20codification.%3CBR%20%2F%3ETry%20to%20copy%20the%20values%20and%20paste%20them%20as%20values%20(Paste%20Special%20%26gt%3B%20Values)%20in%20a%20new%20workbook.%3CBR%20%2F%3EThem%20apply%20the%20SUM()%20formula.%3C%2FLINGO-BODY%3E
New Contributor

Hi Folks.  Apologies from the outset: I'm not great with Excel.

 

I'm using an simple Excel spreadsheet that I set up a long time ago.  I have a "Totals" column where the final cell contains a formula that is copied from the cell above it which in turn is copied from the cell above that one, etc., etc.  So far the copy has worked over 1500 times.  The actual formula reads:    =SUM(S1975+P1976-Q1976) and so the resulting value changes according to what figures are entered in the P and Q cells. However all I get is a ~VALUE! message.  I've tried entering the formula myself rather than copying it but I still get the same error message.  I can see no mistakes in the figures I've put it into P and Q cells and no error in the formula I'm putting in manually.  Help, please!  

3 Replies
There is nothing wrong with your formula.
The problem is with the values. Maybe some text value but showing as numbers, or different codification.
Try to copy the values and paste them as values (Paste Special > Values) in a new workbook.
Them apply the SUM() formula.
Thank you Juliano.

Your reply prompted me to copy the whole workbook into a new one and it's working perfectly! Thank you so much. Best regards. Roger
You are welcome.
If it worked please hit the like button and mark it as solved.