Summing records by criteria with Grid's database functions

Long before filters and pivot tables, spreadsheets had a neat way to ask questions of a list: the database functions. Grid by Zaweba has all twelve. They summarise only the records that match a set of criteria, and the criteria live in cells on the sheet, so changing the question is as simple as typing a new value.

The twelve functions

DSUM adds a field for matching records. DAVERAGE averages it. DCOUNT counts matching records with a number in the field, and DCOUNTA counts those with anything in it. DMAX and DMIN find the largest and smallest values. DPRODUCT multiplies them. DSTDEV and DSTDEVP give the standard deviation for a sample and a whole population, and DVAR and DVARP the variance. DGET returns a single value from the one record that matches, and gives an error if none or more than one do.

How the three arguments fit together

Every database function takes the same three things. The first is the database: the whole list, including its header row. The second is the field: either the header text in quotes or the column's position in the list. The third is the criteria range: a small block of cells with header names on the first row and the conditions underneath.

Say you have a sales list with columns for Date, Region, Product and Amount. Somewhere above or beside it, type Region in one cell and North underneath. A DSUM pointing at the list, the field "Amount" and that two-cell criteria range adds up every sale in the North. Type South over North and the total changes straight away.

Building richer criteria

Conditions on the same row of the criteria range must all be true, so Region North and Product Chairs side by side gives sales of chairs in the North. Conditions on separate rows are alternatives, so North on one row and South on the next gives sales in either region. Comparisons work too: a criterion of ">500" under Amount picks out the larger sales.

Repeating a header in the criteria range lets you set a span on one field, such as a Date column with ">=" a start date and another Date column with "<=" an end date. That's a tidy way to report a month or a quarter.

When to use them

Database functions shine on a report sheet with a small criteria panel at the top. Someone who doesn't write formulas can change the region or the date span and see every figure update. They're also common in older workbooks, which open and calculate in Grid as normal.

For a quick look at the totals by every region at once, a pivot table is usually faster. For a single condition, the familiar conditional sum and count functions are shorter to write. The database functions are at their best when the conditions are several, change often, and should be visible on the sheet rather than buried in formulas.

Small mistakes to avoid

Headers in the criteria range must match the list's headers exactly, so copy them rather than retyping. Don't leave a blank row in the criteria range, because a blank row matches every record and the total jumps to the grand total. Keep the criteria range away from the list itself, ideally above it or on another sheet, so it isn't caught up when you sort or filter. Giving the list a name with Define Name, and updating it as the list grows, keeps every database argument pointing at the right rows.

With a list, a criteria panel and a few database functions, Grid by Zaweba gives you a small reporting tool that anyone in the house or office can use.