DSUM: How to define the criteria range using braces?

Question

I think that it's easier to explain my problem with an example:

Based on the spreadsheet above, the formula =DSUM(A4:D8,B4,A1:A2) works, returning 20.

Why =DSUM(A4:D8,B4,{"OrderID";">10567"}) does not work? (It returns #VALUE!)

Answer 1

From DSUM documentation

Criteria is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

VLOOKUP uses an array of values.

table_array Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name.

Answer 2

Based on what's said about D-Functions on this page, I think you need to have the criteria in a separate cell.

EDIT: If the goal of including the criteria in the formula is to make it more readable, you could work with named ranges instead.

EDIT 2: In response to your comments.
It's not possible to do what you want (include the criteria in the formula) because of how the DSUM() function works. Take a look at the documentation for DSUM and compare it with VLOOKUP:

The syntax for the DSum function is:

DSum( range, field, criteria )

range is the range of cells that you want to apply the criteria against.

field is the column to sum the values. You can either specify the numerical position of the column in the list or the column label in double quotation marks.

criteria is the range of cells that contains your criteria.

Note the difference:

The syntax for the VLookup function is:

VLookup( value, table_array, index_number, not_exact_match )

value is the value to search for in the first column of the table_array.

*table_array* is two or more columns of data that is sorted in ascending order.

*index_number* is the column number in table_array from which the matching value must be returned. The first column is 1.

As DSUM is looking for a range of cells that contain the criteria, there's nothing you can do to avoid passing it just that - a range of cells.

I think the best you can do is define your different criteria as named ranges, which will make it a lot easier to reference the different ones depending on what you want to do in the formula. Unfortunately, if the regular SUM function is not fast enough for you, there's not much else you can do - you will have to specify criteria in cells to use DSUM.

Answer 3

Dsum sucks, try this: =SUMPRODUCT((A5:A8>10569)*(B5:B8))

Generally I would advise you not to hard code stuff like this, it's "bad practice", but as you like.