Zero and Empty cells, What to do

[Amnon Wilensky]

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

 

[browniebodrum]

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.

 

[Kelly]

I have used Conditional Formatting in the past. Highlight your spreadsheet
and use "Conditional Formatting" to put 0's in white text.

 

[Pete_UK]

Put this formula in D2:

=IF(AND(ISNUMBER(B2),ISNUMBER(C2),B2>0),B2*C2,"")

and copy down.

Hope this helps.

Pete

 

[Amnon Wilensky]

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

 

[Amnon Wilensky]

Thanks,
Pete_uk gave the elegant answer and I think it will help you the next time
Amnon

 

[Amnon Wilensky]

Thanks,
Pete_uk gave me the elegant answer and I think it will help you the next
time
Amnon

 

[Pete_UK]

You're welcome, thanks for feeding back.

Pete

Thanks Pete_UK,

Put this formula in D2:

=IF(AND(ISNUMBER(B2),ISNUMBER(C2),B2>0),B2*C2,"")

and copy down.

Hope this helps.

Pete




- Show quoted text -