Look Who it Was Random video

Excel - Master the OFFSET Function in Excel: Easily Sum Variable Number of Cells - Episode 837

552 views · Published 8 January 2009 · 2:58 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Master the OFFSET Function in Excel: Easily Sum Variable Number of Cells.

Welcome back to the MrExcel netcast, where we tackle all your Excel questions and provide you with the best solutions. In this episode, we will be discussing a common problem faced by many Excel users - how to add up a variable number of cells. 

During the Southeastern Accounting show, I was approached by a member of the audience who had a spreadsheet on his laptop and needed to sum up a certain number of cells. However, the number of cells to be summed up changed every time, making it a challenging task. Luckily, there is a function that can easily solve this problem - the OFFSET function. 

The OFFSET function may be obscure, but it is extremely flexible and can be used for various purposes. It takes five arguments, with the first one being the starting cell. In this case, we will start with cell A5. The next two arguments specify how many rows and columns we want to move from the starting cell, but in this instance, we want to start at A5 and not move anywhere, so we will use 0, 0 for these arguments. 

The fourth argument is where things get interesting. This is where we specify the height of the range we want to add up. In our example, we want the range to be as tall as the number in cell C5. Finally, the fifth argument is for the width of the range, which in this case is just one column. This will return a range of cells, so we need to wrap it in a SUM function to get the total sum. 

To test this out, we can change the number in cell C5 and see that the sum changes accordingly. This is a perfect solution for situations where the number of cells to be summed up varies. The OFFSET function may be obscure, but it is a powerful tool that can make your Excel tasks much easier. Thank you for watching, and don't forget to tune in for more helpful tips and tricks on the MrExcel netcast.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Interesting question from the Southeastern Accounting Show: I want to sum from cell A5 and sum down a certain number of cells. However, the number of cells is stored in cell C5!

Episode 837 will show you the obscure but flexible OFFSET function that can solve this problem.

Table of Contents:
(00:00) Introduction
(00:22) Using OFFSET function to solve problem
(00:44) Explanation of OFFSET function
(01:01) Specifying range for OFFSET function
(01:24) Using SUM function to return range of cells
(01:48) Wrapping OFFSET function in SUM function
(02:22) Interesting use for obscure function
(02:36) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Obscure function
OFFSET function
Range height
Range width
Rows and columns
Spreadsheet
Starting point for a range
SUM function
Summing cells
Yellow cell

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152024/

More from this channel