IF and SUMIF

[JBoyer]

So this is what I want to do...
Sum of all negative numbers in range, if there are no negative numbers in
range display 0, if cells in range are blank display "". I tried using
something like this:


IF(B6:E6="","",SUMIF(B6:E6,<0,B6:E6))


Not sure how to do this, hope you can help!

 

[Mike H]

Try

=IF(ISBLANK(B6:E6),"",MIN(SUM(IF(B6:E6<0,B6:E6,FALSE)),0))

Attay entered with Ctrl+Shift+Enter and Excel will put curly brackets around
it {}

Mike

 

[JBoyer]

I tried it, and what happens is, it displays 0 if there are no negatives, ""
if cells are blank, but not the sum if there are negatives to add. I think i
did it the way you wanted me to try, although I'm confused with what you
wanted me to do with the brackets.

 

[Mike H]

Hi,

Enter the formula in a cell and while still in that cell tap

Ctrl+Shift+enter

Excell will add a set of curly brackets around the formula {} You can't type
these yourself.

Mike

 

[JBoyer]

Thanks alot!

Hi,

Enter the formula in a cell and while still in that cell tap

Ctrl+Shift+enter

Excell will add a set of curly brackets around the formula {} You can't type
these yourself.

Mike

 

[Mike H]

Glad I could help and thanks for the feedback

Mike

 

[Sandy Mann]

Mike,

Doesn't your formula work just as well without the MIN() function?

=IF(ISBLANK(B6:E6),"",SUM(IF(B6:E6<0,B6:E6,FALSE)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

Hmmmmmm

What's the expression KISS
Yes it does well spotted

Mike

 

[GoBow777]

JBoyer:

This should work for you.

=IF(ISBLANK(B6:E6),"",IF(SUMIF(B6:E6,"<"&0,B6:E6)=0,"",SUMIF(B6:E6,"<"&0,B6:E6)))

 

[Sandy Mann]

To comply with the OP's requirement of returning a 0 for all positive
numbers I think that your second "" need to be a zero:

=IF(ISBLANK(B6:E6),"",IF(SUMIF(B6:E6,"<"&0,B6:E6)=0,0,SUMIF(B6:E6,"<"&0,B6:E6)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk