Excel Sum #REF error because it's summing a table I don't want it to

Hi guys,

Using Excel 2010 on Windows 7 professional.

Weird one: I'm summing some table totals (Not absolute references) so I can just copy and paste across multiple sheets. Worked fine on one sheet. Next workbook (Same format) it's screwed, it's actually preventing simple sum calculations and now won't work on any except on the original workbook.

=sum(D10:F10) comes back with a #REF error. I did some digging, if I add the numbers individually it works(=D10+E10+F10...etc) but it won't sum otherwise, so it's not formatting. =sum(D10, E10, F10...) etc won't work either.

What I did notice is that every time I typed "=SUM(", it would highlight a table at the bottom of the sheet, even though I don't want it to. Even if I typed in a column/ row A1: 1, A2:1, A3: 3, all formatted as numbers and did a =sum(A1:A3), it would come back with a #REF error because it would have a weird blue highlight on a table at the bottom of the sheet. But the blue highlight didn't appear in the formula bar, i.e no Table5 or A20:F30, no references, I could just it was highlighting the table

Going further, when I type =sum, it gives a drop down of the different sums I can do, it provides a sum table (By the looks) or a sum Fx. It will not allow the Sum Fx and defaults to the sum table and puts a blue highlight box around the table at the bottom.

The only way to fix this I've found is to convert the offending table to a range and back again to a table (The absolute references are used elsewhere and I prefer it as a table, it's just for this exercise I require moving references). But I see converting every table to a range and back as defeat, any ideas on what is causing this? The moment I converted to and back, it stopped providing the weird sum table option, stopped unusually highlighting the table at the bottom and allowed all further sums to work.

I can't work out why it won't sum/ refuses to not highlight an irrelevant to the sum table. Prior to this I used far more memory intensive array formulas before converting them to simple values to save memory, so I can't work out why it won't do a simple sum.

Any help is much appreciated,

Kind regards and many thanks,

Leon