Maximum Character Length per Formula

Microsoft Excel 2003 (full product)
November 19, 2009 at 14:21:40
Specs: Windows XP
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

See More: Maximum Character Length per Formula

Report •

November 19, 2009 at 15:03:00

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 rows

3. 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.


Report •

November 20, 2009 at 10:36:38
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 works

Thanks so much.

Report •

November 20, 2009 at 14:44:05

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	6

The 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
5	Template	Type		Department	Total
6	AMD-001		Standard	Sales		5

If 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:


The formula in cell J6 is:

Change DSUM in J6 to DCOUNT and you get 2, i.e., two records match your criteria.

Hope this is of interest.


Report •

Related Solutions

Ask Question