Cringe-worthy because I realize this obscure fact likely lead to dozens of wrong reports in my career. The problem starts with a question from Derek about why his DSUM formula is not working. It seems to be a bug, but other Excel MVPs say it is working as designed. Then @ExcelisFun makes it even worse by telling me that Advanced Filter has the same design! Watch how both DSUM and Advanced Filter both seemingly return wrong results. Then, a workaround to get exact match in the criteria range. Finally, a plug for the new Excel MVP book available at Excel Insights book by Excel MVP
Transcript of the video:
Learning Excel from MrExcel Podcast Episode 2305.
Advanced Filter, and DUM both Default to "Begins With".
Hey welcome back to the MrExcel Netcast. I'm Bill Jelen.
I learned something in this one and this must be the this must be the time of year for learning something new.
If you followed Leila - on her channel two months ago, she had this "surprising" Advanced Filter trick, something she had never heard of in in years of using Advanced Filter and I know that trick, but I just learn something new in the process of this video.
I received an email from a friend of mine, Derek. He works at a bank.
He has a big complicated spreadsheet and the DSUM function is not working for him.
And I'm like, Oh my gosh, so you know, I try to figure it out and discovered a bug 0 an apparent bug in DSUM.
I simplified Derek's really complicated spreadsheet down to the simple one here, and actually once I realized that there was a problem. I Tweeted it out to my Twitter channel.
I said, Alright, you know, without looking in Excel help, what would this function return?
The DSUM function is taking this database, asking for the third column?
And we're looking for all sales of Andy+Apple and Betty+Apple.
So Andy Apple should be 1 + 8 + 16.
Should be 25, but what is it really?
=DSUM( Of this database up here.
So choose that range, comma field is 3 and with the third column and then the criteria is this little criteria range here.
DSUM goes all the way back to the early 90s - an ancient function, but in my mind it is giving me the wrong answer.
I should be getting 1 + 8 + 16, but what it's doing is it's giving me everybody.
Well every all the sales of Andy and Betty it's throwing Charlie out because Charlie is not on the list.
But Apple, Apple sauce, Apple juice.
All showing up right and I'm convinced this is a bug because I have been using DSUM forever.
I used to use it all the time and I don't remember this behavior at all, so I went out.
The Excel MVP's have a mailing list that is monitored by the Excel team, so I sent a note out there.
I said Hey Look, there's a bug in DSUM and very quickly very quickly.
Roger and Mike both came back and said no, hey Bill, you're wrong.
You don't understand how it works DSUM (and by the way advanced filter too) always treat the criteria as Begins With.
You don't need Apple asterisk. It just happens. I'm like "What?" Advanced filter too?.
And sure enough here, let's do an Advanced Filter. So we start here.
Data. Filter, Advanced filter.
I'm going to copy it into the location.
The criteria range is Andy, Betty, selling Apple and we're going to copy to this cell right here.
By the way, this is the trick in Leila's video.
She was able to reorder the output range and leave invoice out.
Choose OK, and it's not just giving me the apples.
It gives me applesauce and Apple juice without me having to put an asterisk there. And I had to cringe. I never knew this.
I used advanced filter all the time at my last day job from 1989 to 1999.
And there are probably now, thinking back, dozens of wrong reports at that company! They're out of business and no one is going to care at this point. But holy smokes.
How could this have been the deal?
Sure enough out on Twitter, Duncan Williams - he knew it. He knew the solution.
Looks like Robin Miller knew the solution, an Milan Bortel. And hey.
Shout to Ed Hansberry.
He came up with a completely different formula that solves the problem, but we're talking about DSUM and advanced filter here, so here is the solution.
The solution to prevent the begins with.
You have to explicitly type equal sign, quote, equal sign, what you're looking for, end quote.
Check this out so equal sign.
Start quote.
Type another equal sign. Apple. Andcopy that down and then we get the 1 + 8 + 16.
It's also solution in advanced filter, so here you can put [ typing ] equal sign, quote, equal sign, Apple.
And now when we do the advanced filter.
Copy tp another location. Here's our criteria range.
Copy to these these three cells.
Click OK, we only get the Apple Records, so when I go back to Derek with this, he's like "What?!".
And Derek frankly is one of the smartest Excel guys I've ever met. Alright, so here's why I love this.
There's a lot of Excel MVP's out there and I love the fact that even the people who write a blog and podcast about Excel all the time still have the opportunity to learn something completely new.
Now we invited all of the Excel MVP's to come together and write a book. The book is called Excel Insights.
Every person there on that cover is an Excel MVP from around the World who contributed a chapter with their favorite tips.
Roger - top right hand corner.
He talked about advanced filter all kinds of great advanced filter tricks.
Check out this book click that "I" in the top right hand corner.
The book will be coming to Amazon on April 1st. But we have some early copies available.
Now click that "i" in the top right hand corner. And you can buy the book today.
Hey, if you like what you learn today and you want to learn more, please subscribe and ring that bell.
Feel free to post any questions in the comments below.
I want to thank you for stopping by I'll see you next time for another netcast from MrExcel.
Advanced Filter, and DUM both Default to "Begins With".
Hey welcome back to the MrExcel Netcast. I'm Bill Jelen.
I learned something in this one and this must be the this must be the time of year for learning something new.
If you followed Leila - on her channel two months ago, she had this "surprising" Advanced Filter trick, something she had never heard of in in years of using Advanced Filter and I know that trick, but I just learn something new in the process of this video.
I received an email from a friend of mine, Derek. He works at a bank.
He has a big complicated spreadsheet and the DSUM function is not working for him.
And I'm like, Oh my gosh, so you know, I try to figure it out and discovered a bug 0 an apparent bug in DSUM.
I simplified Derek's really complicated spreadsheet down to the simple one here, and actually once I realized that there was a problem. I Tweeted it out to my Twitter channel.
I said, Alright, you know, without looking in Excel help, what would this function return?
The DSUM function is taking this database, asking for the third column?
And we're looking for all sales of Andy+Apple and Betty+Apple.
So Andy Apple should be 1 + 8 + 16.
Should be 25, but what is it really?
=DSUM( Of this database up here.
So choose that range, comma field is 3 and with the third column and then the criteria is this little criteria range here.
DSUM goes all the way back to the early 90s - an ancient function, but in my mind it is giving me the wrong answer.
I should be getting 1 + 8 + 16, but what it's doing is it's giving me everybody.
Well every all the sales of Andy and Betty it's throwing Charlie out because Charlie is not on the list.
But Apple, Apple sauce, Apple juice.
All showing up right and I'm convinced this is a bug because I have been using DSUM forever.
I used to use it all the time and I don't remember this behavior at all, so I went out.
The Excel MVP's have a mailing list that is monitored by the Excel team, so I sent a note out there.
I said Hey Look, there's a bug in DSUM and very quickly very quickly.
Roger and Mike both came back and said no, hey Bill, you're wrong.
You don't understand how it works DSUM (and by the way advanced filter too) always treat the criteria as Begins With.
You don't need Apple asterisk. It just happens. I'm like "What?" Advanced filter too?.
And sure enough here, let's do an Advanced Filter. So we start here.
Data. Filter, Advanced filter.
I'm going to copy it into the location.
The criteria range is Andy, Betty, selling Apple and we're going to copy to this cell right here.
By the way, this is the trick in Leila's video.
She was able to reorder the output range and leave invoice out.
Choose OK, and it's not just giving me the apples.
It gives me applesauce and Apple juice without me having to put an asterisk there. And I had to cringe. I never knew this.
I used advanced filter all the time at my last day job from 1989 to 1999.
And there are probably now, thinking back, dozens of wrong reports at that company! They're out of business and no one is going to care at this point. But holy smokes.
How could this have been the deal?
Sure enough out on Twitter, Duncan Williams - he knew it. He knew the solution.
Looks like Robin Miller knew the solution, an Milan Bortel. And hey.
Shout to Ed Hansberry.
He came up with a completely different formula that solves the problem, but we're talking about DSUM and advanced filter here, so here is the solution.
The solution to prevent the begins with.
You have to explicitly type equal sign, quote, equal sign, what you're looking for, end quote.
Check this out so equal sign.
Start quote.
Type another equal sign. Apple. Andcopy that down and then we get the 1 + 8 + 16.
It's also solution in advanced filter, so here you can put [ typing ] equal sign, quote, equal sign, Apple.
And now when we do the advanced filter.
Copy tp another location. Here's our criteria range.
Copy to these these three cells.
Click OK, we only get the Apple Records, so when I go back to Derek with this, he's like "What?!".
And Derek frankly is one of the smartest Excel guys I've ever met. Alright, so here's why I love this.
There's a lot of Excel MVP's out there and I love the fact that even the people who write a blog and podcast about Excel all the time still have the opportunity to learn something completely new.
Now we invited all of the Excel MVP's to come together and write a book. The book is called Excel Insights.
Every person there on that cover is an Excel MVP from around the World who contributed a chapter with their favorite tips.
Roger - top right hand corner.
He talked about advanced filter all kinds of great advanced filter tricks.
Check out this book click that "I" in the top right hand corner.
The book will be coming to Amazon on April 1st. But we have some early copies available.
Now click that "i" in the top right hand corner. And you can buy the book today.
Hey, if you like what you learn today and you want to learn more, please subscribe and ring that bell.
Feel free to post any questions in the comments below.
I want to thank you for stopping by I'll see you next time for another netcast from MrExcel.
