I am using the Data-filter-Auto filter function to filter data based on 3 different criteria in Excel. Then I need to summarize one row of data. This row of data has hidden rows. Is there a limit to the number of cell or cell groups that I can add to the sum formula?

I can get subtotal to work, but when I re-sort, I lose the value? Any ideas? Thanks

Hi, 1. When you say you are using three filters, do you mean that you have autofilter active over a series of columns and that you are filtering on three of these columns.

If so, this results in some rows being hidden - rows which do not match the criteria.

2. Is the data you want in a hidden row? You say

This row of data has hidden rows3. You refer to Sum and Subtotal.

What formula are you using. Please post it.4. If you use SUBTOTAL() with one of its options such as 109 (SUM)

the result will change whenever the filter changes or is removed.

To keep a result, use Copy on the cell with the result, then Paste Special - Values in another cell and you will have a copy of the 'transient' data.Note that data saved with Paste Special - Values will not change if the original source data changes.

If it was essential to capture this data on a regular basis it might be possible to capture it with an 'On Change' event, using some VB code.

Regards

Thank you for the information-

1. yes, the active filters hide data that I am not interested in.

2. No, I am not interested in the hidden data

3. I was using SUM- but there is a limit on the # of characters I can sum.

4. Subtotal using 109 did work, but you are correct in that it changes the data when my filters are changed. I realize I can cut/paste/paste special, but I am not interested in doing that. However, it seems that I will have to use Subtotal (109), get the value and then paste the value. Not want I was hoping to do, but it worksThanks so much.

Hi, You could try Excel's database functions such as DCOUNT() and DSUM()

These allow you to setup various criteria (like filters) for a database and each one will return the result from your database, so different 'filters' can be applied and each one returns a value.

Changing one set of criteria will not erase the value returned by another set of criteria.

Here is an example:

Put this data in cells B2 to E11

B C D E 2 Template Type Department Number 3 AMD-001 Standard Sales 2 4 AMD-002 Ad-hoc Sales 1 5 AMD-003 Standard Maintenance 4 6 AMD-001 External Small systems 2 7 AMD-002 Standard Main assembly 5 8 AMD-003 Standard Sales 1 9 AMD-001 Standard Sales 3 10 AMD-002 Ad-hoc Small systems 5 11 AMD-003 External Maintenance 6The next two blocks are criteria for columns in the database - just like filters, and the Total column contains the result, in this case the sum of values in the Number column, but it could be a count of the number of rows that match the criteria.

G H I J 2 Template Type Department Total 3 AMD-003 Standard Sales 1 4 5 Template Type Department Total 6 AMD-001 Standard Sales 5If you add drop downs to cells

G3, H3, I3 and

G6, H6 and I6

you can quickly filter the data and see the results in the final column.

The totals in J3 and J6 are independent, and you can create as many of these result blocks as you want.If a value under a column header is left blank, that column won't be filtered.

The formula in cell J3 is:

=DSUM($B$2:$E$11,"Number",G2:I3)

The formula in cell J6 is:=DSUM($B$2:$E$11,"Number",G5:I6)Change DSUM in J6 to DCOUNT and you get 2, i.e., two records match your criteria.

Hope this is of interest.

Regards

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History