O'Reilly logo
  • duncan williamson thinks this is interesting:




It is clear from this section that aggregation formulas can become, very complex and unwieldy. Yes, the count, average and sum functions work but if I were you I would learn the equivalent database functions for these. Look at the COUNTIFS version from here and contrast it with the DCOUNT equivalent:

=COUNTIFS($B$2:$B$13,$E$18,$A$2:$A$13,$D$18) v =DCOUNT(A1:C13,C1,D17:E18)

Now the other two:

=SUMIFS($C$2:$C$13,$B$2:$B$13,$E$18,$A$2:$A$13,$D$18) v =DSUM(A1:C13,C1,D17:E18)

=AVERAGEIFS($C$2:$C$13,$B$2:$B$13,$E$18,$A$2:$A$13,$D$18) v =DAVERAGE(A1:C13,C1,D17:E18)

Look at the consistency of the syntax of the database functions and then compare the COUNTIFS syntax with the syntax of SUMIFS and AVERAGEIFS.