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