SOLVED

SUM function is not updating column range in Table format.

New Contributor

I have created a sample table for maintaining a students records.  Table name is "First_Semister_Result". Table has 3 headings "Students", "Subjects" and "Analysis". Under "Subjects" heading, exam marks are entered for 5 subjects and total marks of those 5 subjects are calculated under "Total" column using SUM function.

 

Vinit0412_3-1668951604753.png

 

Now I have inserted new column After "Maths" subject. I have 6 subjects as below.

 

Vinit0412_4-1668951713793.png

 

SUM function is doing the correct calculations for all 6 subjects but in the formula bar, it still shows range as  =SUM(First_Semister_Result[@[Column3]:[Column7]])

 

Vinit0412_6-1668951886286.png

 

As I have inserted a new column, SUM function should ideally show the range as 

=SUM(First_Semister_Result[@[Column3]:[Column8]])

 

Please advise me on the above issue.

2 Replies
best response confirmed by Vinit0412 (New Contributor)
Solution

@Vinit0412 You are abusing structured tables a bit here.

 

The yellow header row is definitely not part of the table, and you chose to not display the real column names of the table. Your table probably looks something like this when you show the header row.

Riny_van_Eekelen_0-1668954292164.png

In this example I started with columns 1 to 8 and then inserted a column between "Column4" and "Column5" and Excel automatically called it "Column42". The formula in the Total column (i.e. "Column8") is still summing from the first column in the range "Column3" to "Column7" and it included "Column42". Exactly as it should.

Riny_van_Eekelen_2-1668954499653.png

In other words, inserting a column in a structured column does not increment the column numbers towards the right of the inserted column as they merely are texts ending with a numerical character. If you want to show the formula as the sum of Column3 to Column8 You need to rename the columns.

 

 

@Riny_van_Eekelen

Hi, Thanks for your guidance.
After reading your comments, I understood what exactly happening.

Did the changes as per your reply.

Thanks again.