 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Zero and Empty cells, What to do
Hi, I have to sum the numbers in column B *column C in column D. The problem is that the data in column B are not always a numbers and some times are zeros. What is the formula that will take into consideration only the cells with numbers that are greater the zero and will ignore the empty cells? see table below. A B C D 1 Item 1 Qt. Price Total 2 a1 0 10 3 a2 100 11 4 Item 2 5 a3 200 5 6 a4 0 6 7 a5 115 3 Using Excel 2003. Thanks, Amnon I'm sure you'll get some more elegant replies, but when I have had this problem in the past, I have used Ctrl + H to replace each blank cell with 0. Thanks, Pete_uk gave the elegant answer and I think it will help you the next time Amnon > I'm sure you'll get some more elegant replies, but when I have had this > problem in the past, I have used Ctrl + H to replace each blank cell with > 0. I have used Conditional Formatting in the past. Highlight your spreadsheet and use "Conditional Formatting" to put 0's in white text. > Hi, > I have to sum the numbers in column B *column C in column D. [quoted text clipped - 15 lines] > Thanks, > Amnon Thanks, Pete_uk gave me the elegant answer and I think it will help you the next time Amnon >I have used Conditional Formatting in the past. Highlight your spreadsheet > and use "Conditional Formatting" to put 0's in white text. [quoted text clipped - 19 lines] >> Thanks, >> Amnon Put this formula in D2: =IF(AND(ISNUMBER(B2),ISNUMBER(C2),B2>0),B2*C2,"") and copy down. Hope this helps. Pete > Hi, > I have to sum the numbers in column B *column C in column D. [quoted text clipped - 15 lines] > Thanks, > Amnon Thanks Pete_UK, Amnon Put this formula in D2: =IF(AND(ISNUMBER(B2),ISNUMBER(C2),B2>0),B2*C2,"") and copy down. Hope this helps. Pete On Mar 6, 7:48 pm, "Amnon Wilensky" <wilen...@isdn.net.il> wrote: > Hi, > I have to sum the numbers in column B *column C in column D. [quoted text clipped - 15 lines] > Thanks, > Amnon You're welcome, thanks for feeding back. Pete > Thanks Pete_UK, > Put this formula in D2: [quoted text clipped - 30 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.