Thread: Sum only fitered items in a table?

1. 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. 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. That's much simpler - thanks Steve!

Gary

4. FYI, if it's actually a Table, then adding the Totals Row will default to using SUBTOTAL(109,) anyway.

5. 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
•