| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero and Null Sum Function
I have the following formula in place:
=IF(SUM(I14:N14)1,SUM(I14:N14),"") The problem I have is when zeros are legitimately entered in the reference fields the sum formula leaves the cell blank instead of summing the cells across to display a zero. In essence how do I get the field to be blank when the reference cells are blank and display a zero when the reference fields are filled with zeros? I am sure it is simple, but my brain is locked up! Thanks for your help in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Null Sum Function
Perhaps you intended to say not
=IF(SUM(I14:N14)1,SUM(I14:N14),"") but =IF(COUNT(I14:N14)1,SUM(I14:N14),"") or =IF(COUNT(I14:N14)=0,"",SUM(I14:N14)) ? -- David Biddulph "Frustrated by Averages" wrote in message ... I have the following formula in place: =IF(SUM(I14:N14)1,SUM(I14:N14),"") The problem I have is when zeros are legitimately entered in the reference fields the sum formula leaves the cell blank instead of summing the cells across to display a zero. In essence how do I get the field to be blank when the reference cells are blank and display a zero when the reference fields are filled with zeros? I am sure it is simple, but my brain is locked up! Thanks for your help in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Null Sum Function
Try this...
=IF(COUNT(I14:N14),SUM(I14:N14),"") -- Biff Microsoft Excel MVP "Frustrated by Averages" wrote in message ... I have the following formula in place: =IF(SUM(I14:N14)1,SUM(I14:N14),"") The problem I have is when zeros are legitimately entered in the reference fields the sum formula leaves the cell blank instead of summing the cells across to display a zero. In essence how do I get the field to be blank when the reference cells are blank and display a zero when the reference fields are filled with zeros? I am sure it is simple, but my brain is locked up! Thanks for your help in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Null Sum Function
That worked. Thanks for your help!
"David Biddulph" wrote: Perhaps you intended to say not =IF(SUM(I14:N14)1,SUM(I14:N14),"") but =IF(COUNT(I14:N14)1,SUM(I14:N14),"") or =IF(COUNT(I14:N14)=0,"",SUM(I14:N14)) ? -- David Biddulph "Frustrated by Averages" wrote in message ... I have the following formula in place: =IF(SUM(I14:N14)1,SUM(I14:N14),"") The problem I have is when zeros are legitimately entered in the reference fields the sum formula leaves the cell blank instead of summing the cells across to display a zero. In essence how do I get the field to be blank when the reference cells are blank and display a zero when the reference fields are filled with zeros? I am sure it is simple, but my brain is locked up! Thanks for your help in advance. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Failed to save table attributes of (null) into (null). | Excel Discussion (Misc queries) | |||
| how to get a function to return a null value that passes the ISBL. | Excel Worksheet Functions | |||
| IF function and null cell value | Excel Worksheet Functions | |||
| How do you change a NULL value to a Zero when using =MID function? | Excel Discussion (Misc queries) | |||
| How do I set a result of an "if" function to NULL; not 0 or ""? | Excel Worksheet Functions | |||
![Frustrated by Averages[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
