Hi,
I am using LibreOffice 3.5.7.2 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.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
NHE/
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,
Ruben