@ > Home > Contents > DSUM: old but not weak Excel 97+

DSUM is the oldest function for grouped data. It was taken over from Lotus 1-2-3 to provide compatibility and allow Excel a good entrance to the market. A big hassle is its need of a 2-row criteria range, a thing its competitors {SUM}, SUMPRODUCT, SUMIF, SUBTOTAL and Pivot-Table don't have to fight with.

This criteria range can not be simulated in 1 row, be it via INDIRECT, named formulas, VBA or Excel4 macros. However, a way for 1-row-use is using Data Table (=TABLE()), described in http://support.microsoft.com/kb/282851/en-us.

A possible way is to convert criteria from 1 row to 2 rows, then including the headers. DSUM itself then would refer to it via shifting, as by INDEX:INDEX or OFFSET. You find an example here.

You will be surprised: This example DSUM results eight times faster than {SUM} and SUMPRODUCT. Consider the increased n of records in Excel 2007 (16 times), and you probably will be happy for each gain of performance. SUMIF is faster than DSUM, but for combined criteria you will need an extra column along the data.

I guess the simple way of combining criteria makes it that fast. The data is referred only once, unlike the other functions which combine conditions by giving the vectors to be compared again. This stating of vectors can be very useful - but mostly, one does not need this feature.

Test it yourself by following the instructions in the file (a macro has to be put into a new module manually - so Excel will not alert an macro warning).

Note: Inside SUMPRODUCT, use the documented "," rather than the frequently used "*" (for compatibility with {SUM}) between arguments, where possible. In Excel 2000, I calculated 1000 SUMPRODUCTs based on 3 columns of 60,000 lines in 75 secs instead of 105, which is only 67%. The Microsoft-Link here mentions 77%, being more careful.