Everyone can use the SUM function in Excel. It is the most basic and used functions available. In fact, it is the only formula in Excel that has its own keyboard shortcut (Alt + =). However many users find difficulty when adding additional rows to the data which they are summing. The formula has taken cell references and this is causing problems.
OFFSET Function
To overcome this problem and make the SUM function moe dynamic we can use the OFFSET function. Eliminating the problem of adding additional rows of data to the data set that is to be totted.
We can use OFFSET to refer to the location of specific data in an Excel worksheet. It returns the value of a cell that is a specified number of rows and columns away from the active cell.
OFFSET is a reference function. The Syntax is
=OFFSET(Reference, Rows, Columns, [height],[width])
The OFFSET function will first look for a Reference, this is the starting point, the cell that you want the answer to end up in. The function then looks for rows, and this is the number of rows away that you want to move to get the data you require. If you are moving up rows you must use a minus value. The function then looks for Columns, this is the number of columns away that you need to move to get the data you require. Height and width as they are placed inside [] mean that they are optional.
What we will look at in the video below is replacing cell references in the SUM function with the OFFSET function. Now, this might sound a little complicated but to be honest, it's not. This is an Excel Hack that every beginner Excel users should know!
Have a look at the video and it will explain all. And if you find it useful I hope that you will like and share
Looking for some FREE Excel Training? Take Practical Beginner Excel Course FREE
In return for this Excel Tip –OFFSET Function to make the SUM function Dynamic – I ask that you share this post with your friends and colleagues
Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox with Earn and Learn activities.
SIGN UP FOR NEWSLETTER NOW
Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.
We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.