Results 1 to 5 of 5
  1. #1
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Sum only fitered items in a table?

    Is there any relatively simple way to do the following?
    The first picture shows an example Excel (2010) table with no filtering applied. The ‘Cost’ column is summed at the bottom with a simple SUM formula.

    nonfiltered_table.jpg

    In the second picture, the table has been filtered so that only 'Nuts' and 'Bolts' Items are displayed. But the SUM formula still calculates the total of all rows in the column above it, not just the visible ones. Question: Is there a relatively simple way to calculate the sum of the Cost column, to reflect only the values that are displayed in the current filtered view?

    filtered_table.jpg

    Notes:
    1. For a number of reasons, it would make more sense to have everything in a database and query against that. But we’re trying to accommodate a start-up client who can only afford a “simple” Excel-based prototype at this point. So the architecture is not what we would advise, but we’re trying to see if it’s feasible.
    2. If the table were as simple as the example, we could loop through every row in the table, and total up the cost values for just those rows that have ‘Nuts’ or ‘Bolts’ in the Item column. But there are actually potentially 50 different types of items, and there could be say 30 types of items visible in the current filtered view, and 20 types of items not. And there are additional criteria columns (not shown here), included in the filtering. And also it’s doable, but not simple, to return the current filtering criteria from each column. Putting code together to do all that may be doable, but not simple.
    3. A suggestion was made to use ADO and SQL to run queries against the table itself. Again, maybe doable, but not simple.
    4. One thing that would make things simpler would be if you could skip the non-visible rows in the table via their “Hidden” property – but Excel is doing something different from using its simple “Hidden” property when it hides the rows in a filtered view.

    Any ways to do this, short of a massive amount of code?

    Thanks,
    Gary

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Use the SUBTOTAL function=SUBTOTAL(9,D2:d9)Will SUM (the "9" indicates sum) the values of the displayed values from a filter

    Steve

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    That's much simpler - thanks Steve!

    Gary

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, if it's actually a Table, then adding the Totals Row will default to using SUBTOTAL(109,) anyway.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    Rory, thanks for this info too - adding a Totals Row definitely works here.

    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •