Forums +Resources for Success+ SUBTOTAL Formula In Excel Used For Filtering

  • Creator
  • #24660
    Jeff Unruh
    Post count: 42

    Maybe everyone knows this, but to me, it was new. In Excel you can have averages and totals, etc., but did you know if you filter a specific column that the total or average is wrong and displays the information for all records and not the filtered records?

    I ran into this today and because Google is my friend, I went and looked up a solution.

    The solution is to use the formula =subtotal.

    On my example I used starting with cell A1 the following:

    test num
    a 5
    a 10
    b 12
    b 15
    c 18

    The 60 is the total (sum) of the num column. Great, but let’s filter it by “Test” column A.

    Notice it still show 60 as the total, even though it should be showing 15.

    test num
    a 5
    a 10

    Now in the cell that has 60, I put: =SUBTOTAL(9,(B2:B6))

    How do we read this formula? First the 9, means to sum. How do you know what formula to use?

    1 = average
    2 = count
    3 = counta
    4 = max
    5 = min
    6 = produt
    7 = stedev.s
    8 = stedev.p
    9 = sum
    10 = var.s
    11 = var.p

    Any numbers above 100 = that it will also count hidden rows (same options as above).

    Then you will see in the formula: (B2:B6)

    This tells Excel to total from cell b2 through b6.

    So now I use the subtotal and I get these results:

    test num
    a 5
    a 10

    How can we use it in the land business. Let’s say we are doing comps and some are located in a different city, so we have a column called city. If you use subtotal formula, you can then see the average or total, etc. for that specific city. If you did a regular sum formula, it would show you incorrect information if you filtered it by city.

    I love learning and this formula was new to me, so I thought I would share.

    Jeff Unruh

  • You must be logged in to reply to this topic.