Database functions for large datasets

Advanced Excel Functions

Agata Bak-Geerinck

Product Owner Data, Telenet

Aggregating large data sets with conditions

A vegetable basket and an Excel table presenting related orders

White space

  • A record represents individual basket items
  • Data can be summarized per basket, per customer, per shop etc.
  • Conditional aggregation can be applied

White space

Conditional aggregation functions:

  • SUMIFS()
  • AVERAGEIFS()
  • COUNTIFS()
  • ...
1 Image credit https://unsplash.com/@sarascarpa
Advanced Excel Functions

Limitations of conditional aggregate functions

Multiple AND conditions

Count returned Florida orders

Example of Excel table with two AND conditions

=COUNTIFS(B:B,"Yes",E:E,"Florida")

Multiple OR conditions

Count returned Florida OR Joe's shop orders

Example of Excel table with two AND and one OR condition

= COUNTIFS(B:B,"Yes",E:E,"Florida") + COUNTIF(D:D,"Joe's")

Advanced Excel Functions

Database functions for the win!

White space

Family of functions:

  • DSUM( )
  • DCOUNT( )
  • DAVERAGE( )
  • DMIN( )
  • DMAX( )
  • ...

White space

Syntax = the same for all!

Syntax of Excel database functions

  • Database: the data table, incl. headers.
  • Field: what we want to sum, count...
  • Criteria: the AND / OR conditions, incl. matematical operators >, <, <>, and wildcards *, ?
Advanced Excel Functions

Multiple AND criteria

Syntax of DSUM

Example of database and criteria tables in excel

  • DSUM(A1:F6, "Sales", H1:J2)
  • DSUM(A1:F6, 6 , H1:J2)
  • DSUM(A1:F6, F1 , H1:J2)
Advanced Excel Functions

AND / OR criteria!

Syntax of DSUM

Example of database and criteria tables in excel

  • DSUM(A1:F6, "Sales", H1:J2) --> DSUM(A1:F6, "Sales", H1:J3)
  • Important! Blank rows in criteria table equal no condition!
Advanced Excel Functions

Eager to practice?

Advanced Excel Functions

Preparing Video For Download...