Excel Sum formula

[Colleen2]

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

[Micron]

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.

[June7]

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.

[Colleen2]

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

[Colleen2]

Yes that is correct - thank you

[June7]

No screen shot in post.

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

[Micron]

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.