What is the difference between SUM and SUBTOTAL?

Question

In Excel, what is the difference between

SUBTOTAL (9, myrange)

and

SUM (myrange) ?

Answer 1

According to Microsoft, there is no difference, although SUBTOTAL() is more flexible since it has the option of including or ignoring hidden values. I wouldn't be surprised if SUM() (and others like AVERAGE() and STDEV()) were implemented in terms of SUBTOTAL().

Answer 2

The big difference between SUBTOTAL and SUM is that SUBTOTAL can be used repeatedly in the same column for section subtotals and then used again at the end for a grand total. SUBTOTAL(9, myrange) excludes other SUBTOTAL-calculated values within myrange. SUM does not exclude anything, so if you have section subtotals, and use SUM(myrange) for your grand total, your grand total will be double what it should be (since it has counted the "naked" row values in myrange as well as the section subtotals that lie within myrange).