Reaching way back to the archives, the DSUM function allows for some pretty amazing criteria. This video discusses how to build smaller criteria ranges for your DSUM function.
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.
Well DSUM has been around for a long time, and DSUM does all kinds of cool things.
It says “Hey, we have a database A1-G1887, we're going to specify which field we want, G1 is sales in this case, and then specify a criteria range.” Right, in the criteria range, there's all kinds of rules for these criteria ranges.
Like here, I have two different customers with two different products, and so that requires 4 rows.
Now, if I had 10 customers and 20 products, it would be 200 rows of criteria, it 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 that it works is, so we're going to make this for your criteria range up here, I'll, just color yellow, they'll leave the top row blank, nothing in that top row.
And then what you're going to do in the next row is, right, any kind of a formula that evaluates to 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 use the MATCH function, say “Hey, go look at the customer in B2, see If it's in here” then used 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 MATCH 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 wrap that in ISNA and NOTS.
That way I get the TRUEs at the appropriate point in time.
So, to change this, we’ll 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 of these products.
So 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.
A very cool way to go, very obscure though, won’t find many people using DSUM in the first place.
And then, secondly, this side, it gives the dynamic criteria range… (unclear) Well hey, I want to thank you for stopping by, we’ll 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.
Well DSUM has been around for a long time, and DSUM does all kinds of cool things.
It says “Hey, we have a database A1-G1887, we're going to specify which field we want, G1 is sales in this case, and then specify a criteria range.” Right, in the criteria range, there's all kinds of rules for these criteria ranges.
Like here, I have two different customers with two different products, and so that requires 4 rows.
Now, if I had 10 customers and 20 products, it would be 200 rows of criteria, it 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 that it works is, so we're going to make this for your criteria range up here, I'll, just color yellow, they'll leave the top row blank, nothing in that top row.
And then what you're going to do in the next row is, right, any kind of a formula that evaluates to 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 use the MATCH function, say “Hey, go look at the customer in B2, see If it's in here” then used 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 MATCH 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 wrap that in ISNA and NOTS.
That way I get the TRUEs at the appropriate point in time.
So, to change this, we’ll 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 of these products.
So 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.
A very cool way to go, very obscure though, won’t find many people using DSUM in the first place.
And then, secondly, this side, it gives the dynamic criteria range… (unclear) Well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
