Create a "Total" field for an auto populating list

[denverlawyer]

I'm trying to create an excel form that pulls a list of entries from an Object in Salesforce and automatically adds the value of each item in the list to create a total field.  Problem is, the number of items being pulled changes and using a "=sum(a1:a3)" syntax stops adding after row 3 no matter how many new lines have been added.  Is there a way to change the syntax in the Total field to always include the number of rows added in the list.

[#2]

@denverlawyer 

One option is:

• Remove the current total row.
• Convert the data range (including the header row) to a table.
• Tick the check box 'Table Row' for the table (on the Design tab of the ribbon, under Table Tools). 

[#3]

@Hans Vogelaar 

Thanks Hans.  I tried your solution and for some reason it won't let me use the Define Names function to insert the fields from Salesforce.  It seems like as soon as I try and use that function to insert the appropriate field label, Excel deletes it the cell reverts back to blank.  Is there a trick to inserting fields into the cell of a Table?

[#4]

@denverlawyer Try another method then:

Insert an empty row between the data and the total row.

Include the empty row in the SUM formulas, but always insert new rows above that empty row.