Other Links

This is one of the 33 lessons from the
Tutorial on Excel

Lesson 3-6: The SUBTOTAL Function in Excel

The Excel function SUBTOTAL is a function that can execute 11 types of calculations. SUBTOTAL is a very useful formula for analysis of data directly from  the database. It will sum, count, calculate average....of the records that have been filtered in a database.

The syntax is very easy:
=SUBTOTAL(9,A2:A50))

In plain English: Execute calculation number 9 on all filtered records of range A2 to A50. If no filter is applied the calculation is executed using  all the cells in the range.

Calculation types:
1: average, 2: count (numbers), 3: count (non blanks), 4: maximum, 5: minimum, 6: product, 7: standard deviation (sample), 8: standard deviation (population), 9: sum, 10: variance (sample), 11: variance (population)

Types 7, 8, 10 and 11 are for users interested in statistics. As for type 6...you tell me.

This is one of the 33 lessons from the
Tutorial on Excel