Subtotal Formula - Anomaly

[KATHWUN]

I'm using =SUBTOTAL(3,range)to count occurances of each value in a column (as I filter for each value, e.g. "Active" or "Inactive"). I have two problems:

PROBLEM #1: SUBTOTALS DO NOT = TOTAL
The range of rows 3-418 (under the header)total 416: Using the formula,
"Active" subtotals to 243,
"Inactive" subtotals to 172. That = 415.
In trying to account for the 1 missing from the 416 total, I examined the Filter menu which shows: (ALL),(TOP 10),(CUSTOM),ACTIVE, INACTIVE,#NAME?. No blanks or mispellings. Range of Q3-Q418 displays accurately. Can you help solve this anomaly?

PROBLEM 2: COPYING FORMULA
When I copy the above formula to a different column (into same row using same range of
3-418), the totals CHANGE. Shouldn't the (ALL) count remain the same if the range is constant? AND Shouldn't the subtotals add up to the total?
TIA for your Magic -- Kath

[Chris Davison]

does Q3 to Q418 contain your filtered "active/inactive" or does it contain numbers or something OTHER than what you're filtering on ?

any hidden rows ?
any merged cells ?

[KATHWUN]

Q3-Q418 cells contain text only, no numbers. That's why I quoted the filter menu, which would reveal any other content than intended. The "active/inactive" is generated from a formula reading dates in other columns. This MrE-MB provided that for me.
No hidden rows or merged cells, Chris.
BTW, what does #NAME? mean? Could that be it?

FORMULA: =IF(OR(AND(T3>0,T3<$A$1,U3=""), AND(V3>0,U3<$A$1,V3<$A$1)),"Active","Inactive")

[Chris Davison]

On 2002-05-15 15:35, KATHWUN wrote:
Q3-Q418 cells contain text only, no numbers. That's why I quoted the filter menu, which would reveal any other content than intended. The "active/inactive" is generated from a formula reading dates in other columns. This MrE-MB provided that for me.
No hidden rows or merged cells, Chris.
BTW, what does #NAME? mean? Could that be it?

FORMULA: =IF(OR(AND(T3>0,T3<$A$1,U3=""), AND(V3>0,U3<$A$1,V3<$A$1)),"Active","Inactive")

It's not impossible...


Actually, I hate ands and ors. It's not that space you have in the middle there is it ?

shown here with an @ sign :

=IF(OR(AND(T3>0,T3<$A$1,U3=""),@AND(V3>0,U3<$A$1,V3<$A$1)),"Active","Inactive")


The help file on #NAME is as follows :

What does the error #NAME? mean?

The #NAME? error value occurs when Microsoft Excel doesn't recognize text in a formula.

Possible cause Suggested action
Deleting a name used in the formula, or using a name that does not exist. Make sure the name exists. On the Insert menu, point to Name, and then click Define. If the name is not listed, add the name by using the Define command.
Misspelling the name. Correct the spelling.To insert the correct name in the formula, you can select the name in the formula bar, point to Name on the Insert menu, and then click Paste. In the Paste Name dialog box, click the name you want to use, and then click OK.
Misspelling the name of a function. Correct the spelling. Insert the correct function name into the formula by using the Formula Palette.If the worksheet function is part of an add-in program, the add-in program must be loaded. For more information about using add-in programs, click .
Entering text in a formula without enclosing the text in double quotation marks. Microsoft Excel tries to interpret your entry as a name even though you intended it to be used as text. Enclose text in the formula in double quotation marks. For example, the following formula joins a piece of text "The total amount is " with the value in cell B50:="The total amount is "&B50
Omitting a colon ( in a range reference. Make sure all range references in the formula use a colon (, for example, SUM(A1:C10).

[KATHWUN]

Thanks for the DIRECTION on the Error, Chris.
I'll study this further.

Re the space in the formula, it appears to make no difference whether it's there or removed.
Thanks again.
Kath