Ask Your Question
0

How to fix DSUM imported from Google Sheets?

asked 2018-11-03 18:10:31 +0100

comingfromdocs gravatar image

updated 2018-11-03 18:30:10 +0100

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?

edit retag flag offensive close merge delete

Comments

Have a look at this.

ebot gravatar imageebot ( 2018-11-03 18:34:54 +0100 )edit

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.

comingfromdocs gravatar imagecomingfromdocs ( 2018-11-03 18:51:54 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2018-11-03 19:06:25 +0100

erAck gravatar image

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.

edit flag offensive delete link more
0

answered 2018-11-03 20:10:19 +0100

comingfromdocs gravatar image

updated 2018-11-03 20:12:44 +0100

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.

edit flag offensive delete link more

Comments

Note that using the wildcard asterisk depends on the setting under Tools -> Options -> Calc -> Calculate, General CalculationsEnable 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 gravatar imageerAck ( 2018-11-07 13:46:40 +0100 )edit

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.

erAck gravatar imageerAck ( 2018-11-07 13:52:50 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-03 18:10:31 +0100

Seen: 186 times

Last updated: Nov 03 '18