Tom’s Tutorials For Excel: Subtotaling Filtered Data
With the SUBTOTAL function you can perform mathematical operations on visible filtered data.
The SUBTOTAL function wants two pieces of information from you:
(1) The type of operation (SUM, AVERAGE, and so on) you want to perform.
(2) The range of cells you are subtotaling.
The SUBTOTAL function refers to the type of operational function as a function number. There are eleven functions offered by the SUBTOTAL function:
The following pictured examples show five common functions at work:
AVERAGE, SUM, COUNT, MIN, and MAX.
The respective SUBTOTAL formulas are the same in all pictured examples.
In cell F1 for Average: =SUBTOTAL(1,C2:C20)
In cell I1 for Sum: =SUBTOTAL(9,C2:C20)
In cell L1 for Count: =SUBTOTAL(2,C2:C20)
In cell O1 for Min: =SUBTOTAL(5,C2:C20)
In cell R1 for Max: =SUBTOTAL(4,C2:C20)
Example 1 — The data range unfiltered:
Example 2 — Column A filtered between dates:
Example 3 — Column B filtered for a single item:
Example 4 — Column C filtered for numbers greater than 40:
Leave a Reply