Code of Subtotal(range:range)

[hilcom]

Hi all,

I use the function Subtotal(range; range) most of the time.

If the count = 0 (zero) I want to do something; if not do something else.

So that should be simple:

if (Subtotal(R1:R2)= 0 do thing1; do thing2)

but..... the IF function never sees 0 (zero) but instead of this 1.


I tried the Code() function and this shows 49 (one) instead of 48 (zero) when the subtotal() shows 0

I also tries Value() but no success


Anyone?

Bob NL

 

[Hui]

Bob,

I am not sure about your use of Subtotal?


Subtotal should be in the form =Subtotal(Numb, Range)

eg: to Count =Subtotal(2, Range)


Hence your equation should be

=If(Subtotal(2, Range)=0, Do this, Do that)


Refer:http://chandoo.org/wp/2010/02/09/subtotal-formula-excel/

Which includes a list of all the Numb values suitable for use with Subtotal

 

[hilcom]

Hui,

Yes, you are right about the function, it should look like this

if (Subtotal(109, R1:R2)=0; dot this;do that)

but it never sees 0 (zero) when the count is 0 (zero)

This I can control by using Code() of the cell. When the count = 0 (zero) the code is 49 instead of 48.

So I see 0 (zero) but the system does not. ???

Bob

 

[Hui]

Bob,

If I use =IF(SUBTOTAL(109,A1:A10)=0,"Zero","Not Zero")

and the sum of the Range A1:A10 is 0 I get Zero and Not Zero if it isn't


Is your data text and not numbers (It may look like numbers) ?

Check this by a simple =Sum(Range) and check it manually, you should get the same number if it is Numbers, Sum() will return 0 if it is text


Can you post your data for us to see?