In Excel and Google Sheets, you can run simple SQL queries using DATABASE functions instead of relying on only MATH functions.

For example, imagine three people go on a trip together and afterwards want to tally up their individual spending. They may start with a transaction log that looks like this:

Column A shows the date, column B shows the person, column C shows the category, and column D shows the amount spent. There is 1 header row and 9 rows of data in this trip log.
Alice, Bob, and Carol go on a trip and log their expenses in an Excel sheet.

How can Alice calculate her individual spend for the trip?

Using MATH functions

Alice can use the SUMIF function to filter and then sum values over a set of cells:

=SUMIF($B$2:$B$10, "Alice", $D$2:$D$10)

This snippet filters cells in the B column ("Person") so that only those that contain the value "Alice" are considered, and then it sums the corresponding cells in the D column ("Amount") to find the total amount that Alice spent.

If Alice had wanted to filter by an additional column, e.g. the C column ("Category"), she could have used the SUMIFS function instead to include multiple filter conditions.

Using DATABASE functions

Had this trip log been a SQL table, Alice could have calculated her spend on the trip by using this query:

SELECT SUM(Amount)
  FROM table
 WHERE Person = "Alice"

The Excel equivalent for this is the DSUM function:

=DSUM($A$1:$D$10, "Amount", {"Person"; "Alice"})

It accepts three arguments: the table, the column to sum, and the where condition. All three arguments can contain either text values or cell references, but in the case that text is used, the database table and where conditions must be formatted as arrays.

Arrays are identified by opening and closing curly braces, with commas to separate cell values within a row and semicolons to delimit rows from each other. To generate a table that looks like this:

A1 B1 C1
A2 B2 C2

the array to use is this:

{"A1", "B1", "C1"; "A2", "B2", "C3"}

So Alice could include a where condition to find her hotel spending by using this where condition:

{"Person", "Category"; "Alice", "Hotel"}

In addition to DSUM, there are other DATABASE functions available. They include but are not limited to:

  • DAVERAGE
  • DCOUNT
  • DGET
  • DMAX
  • DMIN
  • DPRODUCT

And they are all wonderful! In general, I prefer DATABASE functions over MATH ones because DATABASE functions tend to produce more human-readable equations while MATH ones tend to result in convoluted ones. I can't speak to the performance of one over the other, but... DATABASE functions improve my speediness in debugging, and that should definitely count for something!

At the very minimum, I'm always surprised to learn how powerful Excel sheets have gotten over the decades. Who needs SQL anymore? (Kidding!)