What makes SUBTOTAL especially useful is that it automatically ignores rows that are hidden in a filtered list or table. You can use this feature when you need to count, sum, average, etc. values that are visible. In other words, you can use SUBTOTAL to perform calculations that exclude "filtered out" values.
To sum only the visible rows, use the SUBTOTAL function with function number 9 or 109. In the example shown, a filter has been applied to the data, and the goal is to sum the values in column F that are still visible. The formula used is:
The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN, and more. What makes SUBTOTAL especially interesting and useful is that it automatically ignores items that are not visible in a filtered list or table . This makes it ideal...
The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.