What is the difference between SUM and SUBTOTAL?

In Excel, what is the difference between

SUBTOTAL (9, myrange)

and

SUM (myrange) ?开发者_StackOverflow

---

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).

---

Subtotal() will exclude subtotal values in set range. On top of that, it also takes filtering into consideration -- it will only sum up shown filtered value from set range

---

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().