Sum visible negative cells using formula

S

shbutt

How could I sum visible negative cells in a single column. Could it be
possible with a combination of subtotal and sumif function. Here is my take
on this problem but it fails miserably
=IF(SUMIF(AL2:AL278,"<0",AL2:AL278),SUBTOTAL(109,AL2:AL278))

Please your help is needed.
 
J

Jacob Skaria

<<How could I sum visible negative cells in a single column?

The below formula will do...
=SUMIF(A:A,"<0")

If this post helps click Yes
 
J

Jacob Skaria

Oops...Please ignore the post....


Jacob Skaria said:
<<How could I sum visible negative cells in a single column?

The below formula will do...
=SUMIF(A:A,"<0")

If this post helps click Yes
 
J

Jacob Skaria

The range being A2:A100

=SUMPRODUCT(SUBTOTAL(109,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),--(A2:A100<0))

If this post helps click Yes
 
S

shbutt

Thanks Jacob Skaria for your solution. Here is another formula which fits the
bill but I have used yours which is easy to understand and write. The googled
lengthy formula is:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-MIN(ROW(A2:A100)),0,1)),--(A2:A100<0),A2:A100)

Regards.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top