Forum Discussion
Solved
SUM function is not updating column range in Table format.
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.
Now I have inserted new column After "Maths" subject. I have 6 subjects as below.
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]])
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.
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.
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.
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.
2 Replies
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.
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.
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.
