ExcelBanter - IF and SUMIF

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - IF and SUMIF (https://www.excelbanter.com/excel-worksheet-functions/193463-if-sumif.html)

JBoyer

IF and SUMIF

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

IF and SUMIF

Try

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

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

Mike

"JBoyer" wrote:

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!

JBoyer

IF and SUMIF

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" wrote:

Try

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

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

Mike

"JBoyer" wrote:

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

IF and SUMIF

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" wrote:

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" wrote:

Try

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

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

Mike

"JBoyer" wrote:

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!

JBoyer

IF and SUMIF

Thanks alot!

"Mike H" wrote:

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" wrote:

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" wrote:

Try

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

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

Mike

"JBoyer" wrote:

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

IF and SUMIF

Glad I could help and thanks for the feedback

Mike

"JBoyer" wrote:

Thanks alot!

"Mike H" wrote:

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" wrote:

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" wrote:

Try

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

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

Mike

"JBoyer" wrote:

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!

Sandy Mann

IF and SUMIF

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

Replace @mailinator.com with @tiscali.co.uk

"Mike H" wrote in message
...
Try

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

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

Mike

"JBoyer" wrote:

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

IF and SUMIF

Hmmmmmm

What's the expression KISS
Yes it does well spotted :)

Mike

"Sandy Mann" wrote:

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

Replace @mailinator.com with @tiscali.co.uk

"Mike H" wrote in message
...
Try

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

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

Mike

"JBoyer" wrote:

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!

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

IF and SUMIF

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

Replace @mailinator.com with @tiscali.co.uk

"GoBow777" wrote in message
...

JBoyer:

This should work for you.

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

--
GoBow777

All times are GMT +1. The time now is 02:18 PM.