Basic SUM function not working

It seems that the cells you are trying to sum are formatted as Text. They may look like numbers but in reality they are being treated as Text not numbers.

Copy a blank cells (without any cell content in it) --> Select the cells you were trying to Sum --> Right click on one of the selected cell -> Choose Paste Special --> Select Add and click OK.
Your Sum formula will return the correct output then.

Use a formula to test whether the cell content is a number.

Say one of your cells in the range is A2, try a formula in another cell =ISNUMBER(A2). What do you get, True or False?

You may try something like this

Assuming your range is A2:A10

Formula:

Please Login or Register  to view this content.

It is also displaying a blue arrow, I have been into the formulas tab and selected remove arrows but whether it's there or not (although I have never had one show up before) the 'total' column SUM cells are just refusing to change from zero. If I retry the blue arrow reappears again no matter how many times I select remove arrows.

The only reason that I know of for Excel to automatically display those arrows is if there is a circular reference present. Excel often displays 0 or some other non-answer for circular references, because it does not know how to resolve the circular logic.

Assuming you have the status bar visible, look in the lower left corner of the Excel window and look for a "circular:reference". If you have this circular reference notification, look through your spreadsheet (since it seems related to this =SUM() function, that might be as good a place as any to start), and see if you can figure out where your logic is going in circles and what you need to change so it is no longer circular.