Marc sends in an interesting question about SUMIF. Each time the value is equal to January, he wants to grab a five-cell range! SUMIF doesn't seem up to the challenge, but an array formula with OFFSET does the trick. Episode 842 shows you how.
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!
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.
Well, I'm recording some podcasts ahead of time-- I've got some big trips coming up.
Matter of fact, tomorrow, I start in Montgomery, Alabama-- that's on Wednesday, September 10th-- and then Birmingham, the next day; down to Orange Beach right on the coast; and then Atlanta on Saturday; back to Akron on Monday; and then Springfield, Missouri-- Springfield, Missouri, on Thursday, September 18th-- now, that's the accounting show there.
I'm doing three one-hour seminars, or thereabouts, and then in the Virginia accounting show in Roanoke, on Monday, September 22nd; and then Springfield, Virginia, right outside of Washington, DC, the three-day boot camp with Mike Alexander.
So, if you're anywhere near any of these cities, I'll love to have you come out and check out the seminar, or even get together the night before-- we could talk about VLOOKUPs or something like that.
Drop me a note.
Okay, now, today's question comes in from Mark.
Mark is interested in SUMIF.
He says, "You know, hey, I have this situation where I have five different tax lines and months going across the top, and I want to say, 'hey, if the month across the top is January or February or March, then add up all five cells.'" He said, "SUMIF won't do that; I have to end up putting five different SUMIFS in one, to grab each line." Now, there's probably many ways to do this.
Here's the solution that I sent to Mark, and I'm interested to hear, you know, what solution you would use for this-- I'm sure there's a plenty of ways to do it.
So, I came down and created this formula here-- let's take a look at this formula up in the formula bar.
Basically, I said, "Hey, we're going to check to see if C5 through BB5-- that's all of the headings across the top-- are equal to C18." And if that's true, then what I basically needed to do, is I need to divorce the array formula to have a similar shape-- C5 to BB5.
But then what I did was, I said the offset, I want the offset from that cell; go down two rows-- right here the 2 says we're going down the two rows, which basically gets me to the first number over zero columns; and choose a shape that's five rows tall, one column wide.
So, if it is equal to January, we're going to sum those five cells; otherwise, we're going to use zero.
Now, of course, to do this, we have to do Ctrl+Shift+Enter, and it will, in fact, add up all the January cells.
Let's do a test here.
Choose all those January Salesmen; come down here, 3,820.55, which is the answer that we get.
So, that's working well.
Now, of course, you have to do Ctrl+Shift+Enter to accept this formula.
It's an array formula.
Now, I'd be interested to hear how you would approach this.
Just drop me a note: bill@MrExcel.com.
And when I get back from this big road trip, we'll take a look and see if there's a better way to solve Mark's problem.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
Well, I'm recording some podcasts ahead of time-- I've got some big trips coming up.
Matter of fact, tomorrow, I start in Montgomery, Alabama-- that's on Wednesday, September 10th-- and then Birmingham, the next day; down to Orange Beach right on the coast; and then Atlanta on Saturday; back to Akron on Monday; and then Springfield, Missouri-- Springfield, Missouri, on Thursday, September 18th-- now, that's the accounting show there.
I'm doing three one-hour seminars, or thereabouts, and then in the Virginia accounting show in Roanoke, on Monday, September 22nd; and then Springfield, Virginia, right outside of Washington, DC, the three-day boot camp with Mike Alexander.
So, if you're anywhere near any of these cities, I'll love to have you come out and check out the seminar, or even get together the night before-- we could talk about VLOOKUPs or something like that.
Drop me a note.
Okay, now, today's question comes in from Mark.
Mark is interested in SUMIF.
He says, "You know, hey, I have this situation where I have five different tax lines and months going across the top, and I want to say, 'hey, if the month across the top is January or February or March, then add up all five cells.'" He said, "SUMIF won't do that; I have to end up putting five different SUMIFS in one, to grab each line." Now, there's probably many ways to do this.
Here's the solution that I sent to Mark, and I'm interested to hear, you know, what solution you would use for this-- I'm sure there's a plenty of ways to do it.
So, I came down and created this formula here-- let's take a look at this formula up in the formula bar.
Basically, I said, "Hey, we're going to check to see if C5 through BB5-- that's all of the headings across the top-- are equal to C18." And if that's true, then what I basically needed to do, is I need to divorce the array formula to have a similar shape-- C5 to BB5.
But then what I did was, I said the offset, I want the offset from that cell; go down two rows-- right here the 2 says we're going down the two rows, which basically gets me to the first number over zero columns; and choose a shape that's five rows tall, one column wide.
So, if it is equal to January, we're going to sum those five cells; otherwise, we're going to use zero.
Now, of course, to do this, we have to do Ctrl+Shift+Enter, and it will, in fact, add up all the January cells.
Let's do a test here.
Choose all those January Salesmen; come down here, 3,820.55, which is the answer that we get.
So, that's working well.
Now, of course, you have to do Ctrl+Shift+Enter to accept this formula.
It's an array formula.
Now, I'd be interested to hear how you would approach this.
Just drop me a note: bill@MrExcel.com.
And when I get back from this big road trip, we'll take a look and see if there's a better way to solve Mark's problem.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
