# Excel Sum formula

1. Novice
Windows 10 Access 2016
Join Date
Jan 2022
Posts
26

## Excel Sum formula

Hello, I am trying to write a Sumif formula for a report summary sheet. I am summarizing the data on the Report Tab to my Summary Tab. The formula works but I have to update the
formula every time the grand total amounts goes to another column in the report worksheet. Today the grand total is under column K in my report workbook. How do I update my formula below so that it looks for Grand Total instead of changing the column letter after each refresh?

here is the formula
=SUMIF(Report!T\$1:T\$181,A4,Report!K\$1:K\$181)

Thank you

2. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
Posts
12,260
So yesterday the grand total was somewhere else, like column J but today it is in column K? I think fixing that would be the way to go. How does the data get there in the first place? If that can't be fixed, I can only imagine that your formula would need to use a function that returned the grand total, but how to find it? Perhaps based on a column header text? A cell with the words "Grand Total" beside/above the total?

While I marvel at the Excel formulas that people come up with, they are not my forte so you might wait for a formula suggestion.

3. Why would grand total column change? If you insert a new column, shouldn't the formula automatically adjust because the column letter is relative reference?

So far, methods I found involve an Excel table https://superuser.com/questions/4610...n-excel#461155

Consider using Access for database instead of making Excel work as a database.

Could provide Excel file for analysis. Follow instructions at bottom of my post.

4. Novice
Windows 10 Access 2016
Join Date
Jan 2022
Posts
26
I am summing the total sales for the month and the Column Changes because if a refund was made in current month from a previous month it adds that month to the columns so the grand total column changes - see screen shot below

5. Novice
Windows 10 Access 2016
Join Date
Jan 2022
Posts
26
Yes that is correct - thank you

6. No screen shot in post.

What does "it adds" mean - how does this happen?

7. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
Posts
12,260
Perhaps it is as simple as putting the refund column to the right of the total, although I imagine it's just as possible to always have it there whether you use it or not. Then things don't move around.

Unfortunately you can't just copy and paste images here. See How to Attach Files at the top of the page.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums