sheet summary error
The formulas in sheet summary change based on what column I sort by.. very unhelpful. (Even if I add $ to my formula....) Anyone else run into this bug?
Best Answer

Paul is correct! The reference is in regards to that specific cell, no matter where the cell is moved in the sheet. An absolute reference locks the formula so you can move and dragfill the formula up and down without the reference autochanging as you shift the placement of the formula... however if you move the row with the referenced cell to a different location, the formula's row number will shift to follow that specific piece of data, as Paul outlined.
That's a good question in regards to documentation. There is a Help Center article on creating references in formulas (see here), which identifies that the row numbers are to select a cell (versus locking to a row/placement in the sheet) and reference the data inside that cell.
However there is no description/documentation of what will happen to that row number when you Sort a sheet that I can see. I'll provide feedback on that article to see if we can clarify this; if you wouldn't mind also providing feedback (through the "Was this article helpful?" question on the very bottom right) I would appreciate it!
Thanks,
Genevieve
Answers

What are your formulas?

=SUM($[Difference in Pay]$1:$[Difference in Pay]$117) * 0.87
= 18520.4034
and then when I sort that column.. or any other column.. I get a variety of different numbers..
here is one example when I sorted it A to Z
=SUM($[Difference in Pay]$37:$[Difference in Pay]$117) * 0.87
= 0

Is there a reason you are referencing specific row numbers instead of the entire column?

I understand that you can get the "correct answer" by referencing the Column:Column; but I don't see why logically the output of the formula should change when the order is rearranged.
This is a bit unintuitive.

The reason for this is that your formula is referencing the cell itself and not necessarily the location. If you reference [Column Name]1 and then move that cell to a different location within the sheet, the formula will automatically update to follow that cell.
I believe it is designed this way so that if you have multiple formulas referencing a specific cell and then later realize you want that data moved, you can just move the data and not have to worry about updating all of your formulas for the new location.

Okay, I did not know that. Also, I haven't read that nuance before: separating out location from actual cell. Do you have a reference for that?

@Sofia Fernandez I don't have a reference for that. I am only speaking to personal experience and my own thoughts on "why".
@Genevieve P Are you able to confirm/provide any kind of documentation on the subject?

Paul is correct! The reference is in regards to that specific cell, no matter where the cell is moved in the sheet. An absolute reference locks the formula so you can move and dragfill the formula up and down without the reference autochanging as you shift the placement of the formula... however if you move the row with the referenced cell to a different location, the formula's row number will shift to follow that specific piece of data, as Paul outlined.
That's a good question in regards to documentation. There is a Help Center article on creating references in formulas (see here), which identifies that the row numbers are to select a cell (versus locking to a row/placement in the sheet) and reference the data inside that cell.
However there is no description/documentation of what will happen to that row number when you Sort a sheet that I can see. I'll provide feedback on that article to see if we can clarify this; if you wouldn't mind also providing feedback (through the "Was this article helpful?" question on the very bottom right) I would appreciate it!
Thanks,
Genevieve
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!