Database functions in calc, impossible to create larger tables?


I am using LibreOffice on Ubuntu 12.04.4 LTS.

I set up a database in calc and it is ordered the following way:

Code \ Plaatscode \geoghrapic data
Date1\ Date2 \ Date3 etc.

NDC01\NDC \ geographic data \ number
\ number \ number etc.

NDC02\NDC \ geographic data \ number
\ number \ number etc.

NDC03\NDC \ geographic data \ number
\ number \ number etc.

NHE01\NHE \ geographic data \ number
\ number \ number etc.


So the idea is: Location (three letters), point at that location (the three letters + two numbers).

I want to take the sum of all the points per location. This is possible with the DSUM function.
I tried to integrate it as follows

Plaatscode / Date 1 /date2 etc.

NDC/ formula


Formula: =DSUM(database;column refers to date the column heading ; search criteria)

The last part with the search criteria is where it al goes wrong. I first typed A1:A2. So it gets: plaatscode and NDC. This works perflecty fine. But then I want Plaatscode and NHE. When I drag and drop I get A2:A3 (NDC, NHE) which does not work and when I use the $ symol I get A$1:A3 (Plaatscode,NDC,NHE). This gives me the sum of the data of all NDC and NHE entries (which makes sense). There seems to be no way I can type that I want just A$1 AND A3 as entry for the search criteria.

I have tried arrays and the concatenation symbol to get A1 and A3 together but nothing works to get a nice table.

My current non-perfect solution is to alternate plaatscode and the actual code in the column so i get: plaatscode/ndc/plaatscode/nhe/plaatscode/etc. But this looks awful and is annoying later on.

My next problem is going to be getting the average out of the dates based on which month they are in. I expect similar problems, but I haven’t tried yet because it is essentially the same problem as I am facing now.

I read the manuals, searched ask.libreoffice, looked at the wiki and did this several times until I decided that the answers is not yet out there. Can anyone help me figure this out?

Kind regards,

Care to upload an example file? I don’t think I can follow you here without one.

I think better use SUMIF(Range; Criteria; SumRange) function, seems to me more appropriated on how you want to do.

SUMIF help

Sumif worked perfectly! Thanks a lot!
Shame there is no averageif function, but I managed with sumif and countif in conjunction.
Thanks a lot again!

From 4.x there are AVERAGEIF() and AVERAGEIFS() functions.

Thanks again :-). I will get to a rolling release of linux ASAP, so I can get the latest and greatest of libreoffice.