In today's Dueling Excel Podcast [Episode #1256], Mike and Bill work on a formula that adds up all of the non #N/A values in a range. Traditional Excel meets Excel 2010 - and a few new menu items!
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
AND
"Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen.
For all of your Microsoft Excel needs visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
AND
"Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen.
For all of your Microsoft Excel needs visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
Transcript of the video:
Hey! Welcome back.
It's another Dueling Excel podcast.
I'm Bill John, I'll be joined by Mike Girvin from Excel Is Fun.
It's been a while, but we're back.
Where I have an interesting challenge today figure out how to sum the Non#N/A values.
Let's take a look.
Okay, so we have a little Lookup table over here and then some VLookups.
Now, of course, the problem with VLookups is they might return and N/A and now the challenge today is in my reader, is how we're going to add up those cells but, we want to see the #N/A.
Here my reaction is oh, well, let's just get rid of #N/A with equal if error and say there's an error from out there then we put a zero and copy that down.
And that solves the whole problem because then the sum of those is going to be the sum.
But the person said no, no I need to see the #N/A.
I need to know that those are missing so we're going to control Z to undo, and we have to come up with a formula that's going to ignore the #N/A's.
All right, so, here's my approach Let's see what Mike comes up with.
I bet he's gonna have something much more complicated.
Equal sum if some if says hey, let's go look through this range of values and then the criteria is going to be, I'm gonna put this in quotes, in quotes greater than zero.
All right, now, an assumption here these are salaries.
We're not paying anyone a negative amount of money.
If we were paying on that negative amount of money then, refuse one of Mike's methods.
I think we don't have to put the SUM range because, the SUM range and the original range are the exact same thing.
So, you see some range there is optional it's in square brackets, and so we just to finish this off and we get, 289,777.
Now, let's do a little test here, we'll add a brand new row for Omar.
Yourselves down Omar and we'ill make Omar be 50,000 and rio, Omar shows up here and the total works.
So, I think that will do it.
Mike let's see what you have.
Mike: Thanks, MrExcel. Yeah. It's been a long time since we've done some duels nice to be dueling again.
Hey, I don't have a camera today.
I got the new operating system Windows 7 64-bit totally fast, I love it.
But, my camera is not working with it.
So, I have to do it without a camera.
Hey, sum if greater than zero, is that not beautiful efficient works in all versions.
I might as well show you a new function.
Since, I'm using excel 2010 year a new function called Aggregate.
Now, I'm gonna have to scroll down here.
So, you can see this amazing drop down similar to subtotal, but it does a bunch more 11 right there, those are the same 11 as subtotal and then there's a bunch, a bunch of new ones some amazing things you can do here but, look at this we have a number nine is SUM.
So, I'm just going to put nine comma and then this is the great new argument.
Look at this you have all these options the one we're interested in today is ignore error values.
So, I put a 9 comma 6 comma and then highlight the range.
Now, after a while if you get used to it and you're summing doing this kind of formula.
Just type it out real quick and escape, escape.
I hit the plus sign there and boom.
We have our total ignoring errors and it will ignore whatever, error you have there.
All right, throw it back over to MrExcel.
Bill: Hey, all right, Mike aggregate.
Wow that is a cool way to go I love the feature that they can exclude error sells all kinds of great new items there.
And I wanna thank everyone for tuning in.
Sure to check back next week for another dueling excel podcast.
And a just a quick plug here Mike's new book Slaying Excel Dragons, will be out in October you can pre-order now at Amazon, and of course my book Excel 2010 In-Depth out at Amazon or MrExcel.com.
Thanks for stopping by.
It's another Dueling Excel podcast.
I'm Bill John, I'll be joined by Mike Girvin from Excel Is Fun.
It's been a while, but we're back.
Where I have an interesting challenge today figure out how to sum the Non#N/A values.
Let's take a look.
Okay, so we have a little Lookup table over here and then some VLookups.
Now, of course, the problem with VLookups is they might return and N/A and now the challenge today is in my reader, is how we're going to add up those cells but, we want to see the #N/A.
Here my reaction is oh, well, let's just get rid of #N/A with equal if error and say there's an error from out there then we put a zero and copy that down.
And that solves the whole problem because then the sum of those is going to be the sum.
But the person said no, no I need to see the #N/A.
I need to know that those are missing so we're going to control Z to undo, and we have to come up with a formula that's going to ignore the #N/A's.
All right, so, here's my approach Let's see what Mike comes up with.
I bet he's gonna have something much more complicated.
Equal sum if some if says hey, let's go look through this range of values and then the criteria is going to be, I'm gonna put this in quotes, in quotes greater than zero.
All right, now, an assumption here these are salaries.
We're not paying anyone a negative amount of money.
If we were paying on that negative amount of money then, refuse one of Mike's methods.
I think we don't have to put the SUM range because, the SUM range and the original range are the exact same thing.
So, you see some range there is optional it's in square brackets, and so we just to finish this off and we get, 289,777.
Now, let's do a little test here, we'll add a brand new row for Omar.
Yourselves down Omar and we'ill make Omar be 50,000 and rio, Omar shows up here and the total works.
So, I think that will do it.
Mike let's see what you have.
Mike: Thanks, MrExcel. Yeah. It's been a long time since we've done some duels nice to be dueling again.
Hey, I don't have a camera today.
I got the new operating system Windows 7 64-bit totally fast, I love it.
But, my camera is not working with it.
So, I have to do it without a camera.
Hey, sum if greater than zero, is that not beautiful efficient works in all versions.
I might as well show you a new function.
Since, I'm using excel 2010 year a new function called Aggregate.
Now, I'm gonna have to scroll down here.
So, you can see this amazing drop down similar to subtotal, but it does a bunch more 11 right there, those are the same 11 as subtotal and then there's a bunch, a bunch of new ones some amazing things you can do here but, look at this we have a number nine is SUM.
So, I'm just going to put nine comma and then this is the great new argument.
Look at this you have all these options the one we're interested in today is ignore error values.
So, I put a 9 comma 6 comma and then highlight the range.
Now, after a while if you get used to it and you're summing doing this kind of formula.
Just type it out real quick and escape, escape.
I hit the plus sign there and boom.
We have our total ignoring errors and it will ignore whatever, error you have there.
All right, throw it back over to MrExcel.
Bill: Hey, all right, Mike aggregate.
Wow that is a cool way to go I love the feature that they can exclude error sells all kinds of great new items there.
And I wanna thank everyone for tuning in.
Sure to check back next week for another dueling excel podcast.
And a just a quick plug here Mike's new book Slaying Excel Dragons, will be out in October you can pre-order now at Amazon, and of course my book Excel 2010 In-Depth out at Amazon or MrExcel.com.
Thanks for stopping by.
