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.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Episode 837 will show you the obscure but flexible OFFSET function that can solve this problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Here's another question I got before the Southeastern Accounting show, as I was wandering around in the audience beforehand, someone said, "I have this spreadsheet bottled up on his laptop," he says, "I need to start in this yellow cell-- I need to sum up a certain number of cells-- but the number of cells changes every time." And he had a formula over here that was calculating how many cells to sum up.
So, in this case, maybe it was 7-- now, I'm not sure exactly what the the situation was, but there is a function that will solve this problem.
It's obscure, but it's very, very flexible-- I've used this function for many different things, it's called the OFFSET function.
So, OFFSET, it takes five arguments, and in this case, the first argument-- we always have to start with a cell, we have to start with some corner cell, and that'll be, in this case, Cell A5.
And then, the next two arguments are not useful in this particular instance-- it says, from that cell, how many rows down do we want to go, and how many columns across do we want to go?
Well that is to specify a starting point for a range, and I want the range to start right here in Cell A5-- I don't want to go down any number of rows or columns.
So we just put in 0, 0, to kind of ignore those two arguments.
And then the next thing-- this is the useful one-- how tall of a range do we want?
Well, in this case, we want it to be however many rows tall is here in C5.
And how wide?
Well, just one column-- it's only one column we're adding up.
Now, if you think about this, the offset function is going to return a range of cells-- it's going to return seven cells-- and so we can't just have seven cells come flying back into this one cell.
What we have to do is wrap that in a SUM function.
So, SUM, open parenthesis, and then at the end put a close parenthesis.
And what that should do-- 2868-- let's do a little test here, I'll select those seven cells, and down here in the status bar we'll see that that's 2868.
And then to test even further, let's do, maybe, change to just three cells, and we should see that the 1243 that we're getting there is the answer-- is the sum of these three cells, and down here in the status bar it is 1243, so it seems to be working pretty well.
An interesting use for a very obscure function-- the OFFSET function.
In this particular case, it worked out perfectly.
A person had some sort of a calculation that said, "Hey, we want to add up three cells or seven cells or eight cells," OFFSET was able to handle it tremendously well.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
Here's another question I got before the Southeastern Accounting show, as I was wandering around in the audience beforehand, someone said, "I have this spreadsheet bottled up on his laptop," he says, "I need to start in this yellow cell-- I need to sum up a certain number of cells-- but the number of cells changes every time." And he had a formula over here that was calculating how many cells to sum up.
So, in this case, maybe it was 7-- now, I'm not sure exactly what the the situation was, but there is a function that will solve this problem.
It's obscure, but it's very, very flexible-- I've used this function for many different things, it's called the OFFSET function.
So, OFFSET, it takes five arguments, and in this case, the first argument-- we always have to start with a cell, we have to start with some corner cell, and that'll be, in this case, Cell A5.
And then, the next two arguments are not useful in this particular instance-- it says, from that cell, how many rows down do we want to go, and how many columns across do we want to go?
Well that is to specify a starting point for a range, and I want the range to start right here in Cell A5-- I don't want to go down any number of rows or columns.
So we just put in 0, 0, to kind of ignore those two arguments.
And then the next thing-- this is the useful one-- how tall of a range do we want?
Well, in this case, we want it to be however many rows tall is here in C5.
And how wide?
Well, just one column-- it's only one column we're adding up.
Now, if you think about this, the offset function is going to return a range of cells-- it's going to return seven cells-- and so we can't just have seven cells come flying back into this one cell.
What we have to do is wrap that in a SUM function.
So, SUM, open parenthesis, and then at the end put a close parenthesis.
And what that should do-- 2868-- let's do a little test here, I'll select those seven cells, and down here in the status bar we'll see that that's 2868.
And then to test even further, let's do, maybe, change to just three cells, and we should see that the 1243 that we're getting there is the answer-- is the sum of these three cells, and down here in the status bar it is 1243, so it seems to be working pretty well.
An interesting use for a very obscure function-- the OFFSET function.
In this particular case, it worked out perfectly.
A person had some sort of a calculation that said, "Hey, we want to add up three cells or seven cells or eight cells," OFFSET was able to handle it tremendously well.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
