How About DSUM?

Let’s use the same table as we did with DCOUNT and set it up to perform our function:

I want to know the sum of apples in bushels that have over 1,000 quantity, so I will enter into the Criteria box 1,000.  I will then enter into the Criteria Formula box a formula that references the criteria:  =”>”&E4.  The formula must start with an = sign and the operator needs to be in quotes.  Then insert the connector ( & ) followed by the cell reference of the criteria – E4.

Now it’s time to enter our DSUM formula into the E10 Qty box.  Similarly to DCOUNT, the DSUM structure is:

database = you table of bushels including the header = B3:C24
field = the name of the field in the table you want to count = Qty of Apples = E6
Criteria = the formulas that define the range, including the headers = E3:E4

And the answer to the query is 14,963 apples that are in bushels with a quantity over 1,000.  Since we structured the worksheet to allow you to easily vary the criteria, all you need do is change the number in cell E4 and it will adjust the DSUM formula accordingly.

This could have a wide variety of uses.  Can you think of a few in your daily work?  If you have a fixed quantity that you want to query, instead of creating the range entry boxes in cells E3:E4, you could just enter the values into the formula in E7 as:  >1000.  This will result in the same answer, but give you less flexibility if you need to vary the criteria.

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply