Sum visible negative cells using formula

  • Thread starter Thread starter shbutt
  • Start date Start date
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.
 
<<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
 
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
Click to expand...
 
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
 
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.
 
Back
Top