What is the difference between SUM and SUBTOTAL?

Question

In Excel, what is the difference between

SUBTOTAL (9, myrange)

and

SUM (myrange) ?

Answer 1

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

Answer 2

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