| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Empty cells, What to do
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Empty cells, What to do
I have used Conditional Formatting in the past. Highlight your spreadsheet
and use "Conditional Formatting" to put 0's in white text. "Amnon Wilensky" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Empty cells, What to do
Put this formula in D2:
=IF(AND(ISNUMBER(B2),ISNUMBER(C2),B20),B2*C2,"") and copy down. Hope this helps. Pete On Mar 6, 7:48*pm, "Amnon Wilensky" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Empty cells, What to do
Thanks Pete_UK,
Amnon "Pete_UK" wrote in message ... Put this formula in D2: =IF(AND(ISNUMBER(B2),ISNUMBER(C2),B20),B2*C2,"") and copy down. Hope this helps. Pete On Mar 6, 7:48 pm, "Amnon Wilensky" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Empty cells, What to do
Thanks,
Pete_uk gave the elegant answer and I think it will help you the next time Amnon "browniebodrum" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Empty cells, What to do
Thanks,
Pete_uk gave me the elegant answer and I think it will help you the next time Amnon "Kelly" wrote in message ... I have used Conditional Formatting in the past. Highlight your spreadsheet and use "Conditional Formatting" to put 0's in white text. "Amnon Wilensky" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero and Empty cells, What to do
You're welcome, thanks for feeding back.
Pete On Mar 8, 7:34*am, "Amnon Wilensky" wrote: Thanks Pete_UK, Amnon"Pete_UK" wrote in message ... Put this formula in D2: =IF(AND(ISNUMBER(B2),ISNUMBER(C2),B20),B2*C2,"") and copy down. Hope this helps. Pete On Mar 6, 7:48 pm, "Amnon Wilensky" wrote: 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- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| skip cells with zero values in chart (cells not empty) | Charts and Charting in Excel | |||
| Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
| When I SUM cells & 1 is empty I need the result to be empty not 0 | Excel Discussion (Misc queries) | |||
| How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) | |||
| how do populate empty cells with the contents of populated cells . | Excel Discussion (Misc queries) | |||
Linear Mode
