Summing the Visible Filtered List in Excel 2010

To get the total of the values in the visible filtered rows only, we can use the SUBTOTAL function to retrieve the output.
SUBTOTAL:Returns a subtotal in a list or database.

Syntax: =SUBTOTAL(function_num,ref1,…)
img1

img2

Let us take an example:

We have Product list in column A, Price in column B & Order in column C.
img3

  • In cell C14, the formula would be =SUBTOTAL(9,C2:C13)

img4

  • If we apply filter the list with Orders more than 300, the subtotal function will give the sum of the Order which are more than 300.
  • Select any cell in table & press CTRL + SHIFT + L for applying Filter.
  • Click on Order drop down, select Number Filters.
  • Select Greater than option.

img5

  • The following dialog box will appear. Enter 300 in the box as shown in screenshot below.

img6

  • The table will get filter on the Order list which are more than 300

img7

  • You can use SUBTOTAL to ignore values in hidden rows.

We use hide rows to remove unnecessary information from viewer. You can use SUBTOTAL with function as 109 which will ignore the values that are hidden.

  • In cell C15, the formula would be =SUBTOTAL(109,C2:C13)& we hide row number 10 where Order value is 450 then the result of SUBTOTAL function will be different when using 109 as first argument.

img8

SUBTOTAL(9,…) totals everything except cells with subtotals and filtered cells.

SUBTOTAL(109,…) totals everything except cells with subtotals and filtered cells and hidden rows.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube