Reaching way back to the archives, the DSUM function allows for some pretty amazing criteria. Episode #1232 discusses how to build smaller criteria ranges for DSUM.
This episode is the podcast companion to "Excel 2010 In Depth", by Bill Jelen.
This episode is the podcast companion to "Excel 2010 In Depth", by Bill Jelen.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel 2010 in Depth, chapter 12.
Cool DSUM.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen, with DSUMs been around for a long time, and DSUM does all kinds of cool things.
It says, hey! We have a database, A1 through G1887.
We're going to specify which field G1 is, sales in this case and then specify a criteria range.
Alright! In the criteria range, there's all kinds of rules for these criteria range like here, I have two different customers, with two different products and so that requires four rows.
Now, if I had 10 customers and 20 products would be 200 rows of criteria would just take forever and this part of the book is talking about a cool alternative to building that 200 row criteria range and the way it works is...
So, we're gonna make this for your criteria range up here.
I'll just color yellow.
Leave the top row blank, nothing in that top row.
And then what you're going to do in the next row is write any kind of a formula that evaluates the true or false as if it's pointing at the second row of the database.
So, here I have a list of customers.
I want to see if my customers in the database are equal to any of these customers.
So, I used the match functions.
Hey! Go look at the customer at B2 to see if it's in here, then use ISNA and NOTs.
That way, that comes up to True or False, and then over here I have a list of products, I want any of those products to be true.
So, this one.
I'm taking a look at the mash of F2, that's our product in the database.
Check to see if it's equal to any of those over there, in column R.
And then grab that ISNA and NOTs.
So, that way I get the truth of the appropriate point in time.
So, to change this will just point to that as our criteria range.
Don't forget to include that blank row at the top, really important that you do that and it will automatically calculate the combination of all of these customers and all these products.
So, it both has to be one of these customers and one of these products in order to be true.
Allows you to build a very small criteria range that is doing the work.
You know, hundreds of cells.
Very cool way to go, very obscure.
Don't find many people using DSUM in the first place and then secondly this I think is the dynamic criteria.
Hey! I want to thank you for stopping by.
Will see you next time for another netcast, from MrExcel.
Excel 2010 in Depth, chapter 12.
Cool DSUM.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen, with DSUMs been around for a long time, and DSUM does all kinds of cool things.
It says, hey! We have a database, A1 through G1887.
We're going to specify which field G1 is, sales in this case and then specify a criteria range.
Alright! In the criteria range, there's all kinds of rules for these criteria range like here, I have two different customers, with two different products and so that requires four rows.
Now, if I had 10 customers and 20 products would be 200 rows of criteria would just take forever and this part of the book is talking about a cool alternative to building that 200 row criteria range and the way it works is...
So, we're gonna make this for your criteria range up here.
I'll just color yellow.
Leave the top row blank, nothing in that top row.
And then what you're going to do in the next row is write any kind of a formula that evaluates the true or false as if it's pointing at the second row of the database.
So, here I have a list of customers.
I want to see if my customers in the database are equal to any of these customers.
So, I used the match functions.
Hey! Go look at the customer at B2 to see if it's in here, then use ISNA and NOTs.
That way, that comes up to True or False, and then over here I have a list of products, I want any of those products to be true.
So, this one.
I'm taking a look at the mash of F2, that's our product in the database.
Check to see if it's equal to any of those over there, in column R.
And then grab that ISNA and NOTs.
So, that way I get the truth of the appropriate point in time.
So, to change this will just point to that as our criteria range.
Don't forget to include that blank row at the top, really important that you do that and it will automatically calculate the combination of all of these customers and all these products.
So, it both has to be one of these customers and one of these products in order to be true.
Allows you to build a very small criteria range that is doing the work.
You know, hundreds of cells.
Very cool way to go, very obscure.
Don't find many people using DSUM in the first place and then secondly this I think is the dynamic criteria.
Hey! I want to thank you for stopping by.
Will see you next time for another netcast, from MrExcel.
