FORMULA WHICH IGNORES POSITIVE VALUES

  • Thread starter Thread starter CHAKES
  • Start date Start date
C

CHAKES

HI
I NEED KNOW IF THERE'S A SUM FORMULA {=SUM(A1:A4)} WHICH TOTALLY IGNORES
POSITIVE VALUES AND ADDS ONLY THE NEGATIVE VALUES?

EXAMPLE:
A1= -3
A2= -7
A3= 2
A4= -6
A5= ANSWER WHICH IS OBVIOUSLY -14, BUT I WANT THE FORMULA TO IGNORE THE 2 SO
THE ANSWER WOULD BE -16.
 
Any of the following:
=SUMPRODUCT(--(A1:A4<0)*(A1:A4))
=SUMIF(A1:A4,"<0",A1:A4)

=SUM(IF(A1:A4<0,A1:A4))

The last is an array formula and must be committed with Ctrl + Shift + Enter
(CSE)

Peter Atherton
 
Back
Top