# Database functions in calc, impossible to create larger tables? [closed]

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

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-20 21:11:51.420730

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

( 2014-04-16 22:28:35 +0200 )edit

Sort by » oldest newest most voted

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

SUMIF help

more

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!

( 2014-04-17 13:25:40 +0200 )edit

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

( 2014-04-17 15:51:24 +0200 )edit

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

( 2014-07-10 23:58:52 +0200 )edit