Subtotal < 0

[Guest]

hello,
how can i get less than 0 value in subtotal, now i am trying with follow:
=SUBTOTAL(109,$J$11:$J$3000,"<0")
but it's not working.
thanks

 

[Guest]

Hi Tufail,

It depends on what you want the cell to display. If you want it to display
"<0" when less than zero and otherwise the total then try:

=IF(SUBTOTAL(109,$J$11:$J$3000)<0,"<0",SUBTOTAL(109,$J$11:$J$3000))

But if you only want to display less than zero or greater/equal to zero then:

IF(SUBTOTAL(109,$J$11:$J$3000)<0,"<0",">=0")

I hope this helps,

Sean.

 

[Bob Phillips]

=SUMPRODUCT(SUBTOTAL(109,OFFSET(J10,ROW($J$11:$J$3000)-ROW($J$10),,1)),--($J$11:$C$3000<0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

thank you very much, that's what i was looking !

Hi Tufail,

It depends on what you want the cell to display. If you want it to display
"<0" when less than zero and otherwise the total then try:

=IF(SUBTOTAL(109,$J$11:$J$3000)<0,"<0",SUBTOTAL(109,$J$11:$J$3000))

But if you only want to display less than zero or greater/equal to zero then:

IF(SUBTOTAL(109,$J$11:$J$3000)<0,"<0",">=0")

I hope this helps,

Sean.

 

[Guest]

thank you very much, that's what i was looking !

=SUMPRODUCT(SUBTOTAL(109,OFFSET(J10,ROW($J$11:$J$3000)-ROW($J$10),,1)),--($J$11:$C$3000<0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)