Subtotal data excluding negitive numbers.
 

I have a spreadsheet contains lots information, each one linked to
some other spreadsheet. In column “A” some numbers are positive and
some are negative. What I want to do is to subtotal(9,A3:A100) to show
the total numbers when I filter something out. While I don’t want
these negative numbers to be calculated. I knew if I add a new column
can resolve this problem, but if I the spreadsheet does not allow me
to do that change, how can I put kind of formula in one single cell to
show the subtotal result without adding the negative numbers?

I have tried sumproduct but result only showing the full sum whatever
how you filter it out.

Thanks.

Subtotal data excluding negitive numbers.
 

In column “A” some numbers are positive and
some are negative. What I want to do is to subtotal(9,A3:A100) to show
the total numbers when I filter something out.
... how can I put kind of formula in one single cell to
show the subtotal result without adding the negative numbers?

One way is to use something like
=SUMIF($A:$A,"0")

Another way is to auto-filter column A, choosing
Custom is greater than 0
and
=SUBTOTAL(109,$A:$A)
The code 109 is like 9, but it "ignores hidden values" (ref.: Excel's
built-in Help).

Subtotal data excluding negitive numbers.
 

Try

=SUMPRODUCT(--(A3:A1000),SUBTOTAL(9,OFFSET(A3:A100,ROW(A3:A100)-ROW(A3),0,1)))

Hope this helps,

Hutch

"Tony" wrote:

I have a spreadsheet contains lots information, each one linked to
some other spreadsheet. In column €œA€� some numbers are positive and
some are negative. What I want to do is to subtotal(9,A3:A100) to show
the total numbers when I filter something out. While I dont want
these negative numbers to be calculated. I knew if I add a new column
can resolve this problem, but if I the spreadsheet does not allow me
to do that change, how can I put kind of formula in one single cell to
show the subtotal result without adding the negative numbers?

I have tried sumproduct but result only showing the full sum whatever
how you filter it out.

Thanks.
.

Subtotal data excluding negitive numbers.
 

On Dec 1, 5:20*pm, Tom Hutchins
wrote:
Try

=SUMPRODUCT(--(A3:A1000),SUBTOTAL(9,OFFSET(A3:A100,ROW(A3:A100)-ROW(A3),0,*1)))

Hope this helps,

Hutch



"Tony" wrote:
I have a spreadsheet contains lots information, each one linked to
some other spreadsheet. In column “A” some numbers are positive and
some are negative. What I want to do is to subtotal(9,A3:A100) to show
the total numbers when I filter something out. While I don’t want
these negative numbers to be calculated. I knew if I add a new column
can resolve this problem, but if I the spreadsheet does not allow me
to do that change, how can I put kind of formula in one single cell to
show the subtotal result without adding the negative numbers?

I have tried sumproduct but result only showing the full sum whatever
how you filter it out.

Thanks.
.- Hide quoted text -

- Show quoted text -

Seems it's not working ... :(