Calc spreadsheet file used in video: Right-click to download file
Another group of functions which are very useful in Calc are the D functions. These functions treat a table as a database.
For example suppose you needed to get the totals for 2 or more employees out of a table of sales. this would be a situation where you could use DSUM:
DSUM takes 3 parameters, the first is the entire table, headers and all. The second parameter is the column number (counting from the left) where the values you want to add are located. Last parameter is the criteria, which is located in a separate table, the header in the criteria must match the header in the table your searching. Here are the steps taken for the example above:
This is also possible to do with SUMIFS, but it's a much more complicated formula:
=SUMIFS(C2:C10,B2:B10,A13)+SUMIFS(C2:C10,B2:B10,A14)
For a count of how many sales these employees made use DCOUNT. DCOUNT has the same parameters as DSUM:
To get the average of sales for these employees use DAVERAGE, same parameters as before:
If you want the Minimum and Maximum sales for the 2 employees use DMIN and DMAX, they have exactly the same parameters as all the others.
=DMIN(A1:C10,3,A12:A14)
=DMAX(A1:C10,3,A12:A14)
Here's an example of using DSUM to filter data, I want to get the sales figures of the two employees which are over $7000 between 3/15/15 and 3/17/15. Here's the criteria table I would use,
Note that the columns can be in a different order as long as the headers in the tables match exactly. To get a result in between 2 dates, 2 date columns have to be used.
The formula will be placed in cell E2:
