|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 12th, 2010, 09:05 PM
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
May 12th, 2010, 09:27 PM
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
May 12th, 2010, 09:33 PM
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
May 12th, 2010, 11:19 PM
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. .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
