| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#9
|
|||
|
|||
|
JBoyer:
This should work for you. =IF(ISBLANK(B6:E6),"",IF(SUMIF(B6:E6,"<"&0,B6:E6)= 0,"",SUMIF(B6:E6,"<"&0,B6:E6))) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif | Excel Worksheet Functions | |||
| sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
