12 functions

Database Functions

The "D-function" family — DAVERAGE, DCOUNT, DGET, DSUM. Query structured tables using a separate criteria range. Underused, powerful, and often the perfect fit for form-driven workbooks.

💡 How D-functions differ from SUMIFS & friends

SUMIFS embeds criteria inside the formula. D-functions read criteria from a separate range on the sheet. That means users can change criteria by editing visible cells — no formula editing required. Perfect for search forms, report builders, and interactive dashboards.

Trade-off: D-functions require setting up a criteria range that mirrors your table's column headers. More setup, but much more flexible for end users who don't know Excel formulas.

How the criteria range works

Every D-function takes three arguments: the database range, the field to operate on, and a criteria range. The criteria range is just a small block on your sheet with column headers matching your database and cells below containing the filter conditions.

For example, to sum revenue for the "West" region with orders over $1,000:

  • Create a criteria range with "Region" and "Revenue" headers
  • Put West under Region and >1000 under Revenue
  • Formula: =DSUM(database, "Revenue", criteria_range)

Users can change "West" to "East" in the criteria cell without touching the formula — the result updates automatically.

All 12 database functions

D-functions vs SUMIFS vs Pivot Tables — which to use?

D-functions Best for interactive dashboards where users change criteria in visible cells. Excellent for form-driven workbooks and search interfaces.
SUMIFS & family Best for hardcoded aggregations inside models. Faster to write when criteria are simple and rarely change.
Pivot Tables Best for exploratory analysis with many groupings. Superior for cross-tab and drill-down analysis. Not formula-driven, requires manual refresh.
FILTER + aggregation Modern replacement for D-functions in many cases. =SUM(FILTER(revenue, region="West")) is often cleaner. Requires Excel 365.
Power Query Best for large datasets and reproducible data prep. Overkill for single-cell aggregations.

Common patterns

  • Search-driven dashboard: criteria cells at the top, DSUM/DAVERAGE cards below reading from them
  • Compound criteria (AND): put multiple filters in the same row of the criteria range
  • Compound criteria (OR): put filters in different rows of the criteria range
  • Range criteria: >=1000 and <5000 in adjacent Revenue cells for a range filter
  • Wildcard criteria: West* matches West, Western, Westside, etc.
  • DGET for single lookup: like INDEX/MATCH but with criteria-range flexibility

Frequently asked questions

Are D-functions obsolete?

Not obsolete, but overshadowed. Dynamic array functions (FILTER) and Pivot Tables cover most use cases with cleaner syntax. D-functions remain excellent for interactive dashboards where end users change criteria in visible cells.

Why does DGET return #NUM! or #VALUE!?

#NUM! means multiple records match (DGET requires exactly one). #VALUE! means no records match. Loosen or tighten your criteria to find exactly one row.

Can D-functions work with Excel Tables (structured references)?

Yes. Point the database argument at the whole table (including headers) and D-functions read the column names from the header row. Combining D-functions with Tables gives you self-adjusting aggregations as the table grows.

Do D-functions work with wildcards?

Yes — * (any characters) and ? (single character) both work in criteria cells. Type North* to match North America, Northeast, Northern Region, etc.

Are D-functions available in Google Sheets?

Yes — Sheets has the same D-function family with identical syntax. Cross-compatible for most cases.

Build interactive dashboards faster.

Ask the Add-in "build me a search-driven dashboard using D-functions" — get the criteria layout and formulas ready to paste.

Get the Add-in →