How to fix DSUM imported from Google Sheets?

[comingfromdocs]

I’m trying to import a spreadsheet keeping track of my personal expenses from Google Sheets, but DSUM isn’t working and I haven’t been able to find much online regarding this. What I’m trying to do is, for example, to sum up the expenses (“Amount”) in the “Category” of “Food” during the month of January: =DSUM($‘2018 - Expenses’.$A:$E,“Amount”,{{“Date”;“2018-01*”}, {“Category”;“Food”}})

This is giving me Error:533, which I haven’t been able to find in the documentation. Is this not the correct way to use “Search criteria”? If so, how can something like this be accomplished in LibreOffice?

[Hrbrgr]

Have a look at this.

[comingfromdocs]

Thanks for your reply! That explains the error. I’m still not really sure what the best way would be to accomplish this in LibreOffice, though. Is there a way to sum only elements that meet multiple criteria, like in my example?

Edit: I see the possibility to accomplish this sort of thing using a criteria table. Is that the only way? I would prefer to have the conditions in the formula itself, so I don’t need to make a separate table for each category and can change the formula instead.

[erAck]

If you place the cell cursor on the Err:533 error cell you’ll see in the status line displayed Nested arrays are not supported. Anyway, that’s not how SearchCriteria for D*() functions are to be given (it might be that Google Sheets allows this but it isn’t valid in any other major spreadsheet application).

See DSUM help and the example database range and search criteria at the top of that page

Note that the help doesn’t mention yet the possibility to not only use regular expressions but also simple wildcards in queries. See that help page.

[comingfromdocs]

Thanks everyone for the help!

To anyone that comes accross this in the future, this is the solution I’ve found that works:

Google Sheets example:
=DSUM($‘2018 - Expenses’.$A:$E,“Amount”,{{“Date”;“2018-01*”}, {“Category”;“Food”}})

LibreOffice equivalent:
=DSUM($‘2018 - Expenses’.A:E,“Amount”,{“Date”,“Account”,“Description”,“Category”,“Amount”;“2018-11*”,"*","*",“Food”,"*"})

Where in Libreoffice, you need to make an array where the first entries are the column names separated by commas, then a semicolon, then the values you want to select separated by commas. Any fields you don’t care about should just consist of an asterisk.

[erAck]

Note that using the wildcard asterisk depends on the setting under Tools → Options → Calc → Calculate, General Calculations Enable wildcards in formulas (which is the Excel compatible setting). If that was set to regular expressions instead then the sequence -* would mean “0 or more minus/hyphen characters”.

Note also that the array separators vary between UI languages and are those set under Tools → Options → Calc → Formula, Separators.

[erAck]

Also note that specifying fields with "*" any content takes more processing time (as the criterion is evaluated on each record) than just omitting the field altogether.

Anyhow, using a cell range for the criteria better clarifies things visually and also allows for easier editing of the criteria or adding OR’ed criteria.