sumif problem

P

puiuluipui

i need to make this work =sum(c4*d4), but i have some cells that does not
contain anything and the result looks like that #VALUE!

1 c d e(sum)
2
3 3 5 15
4 2 #VALUE!


I don't want anything to apear in e4, or in the worst case i want 0 to apear
in this cell.
Anyone can help me? Thanks
 
R

Ron Coderre

I'm not sure where the SUMIF come in, but from what you posted, try something
like this:

For values in C1 and D1
E1: =C1*D1

Or to NOT display zero values
E1: =IF(COUNT(C1:D1)=2,C1*D1,"")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
P

puiuluipui

Thanks! works great...Thanks again!

Ron Coderre said:
I'm not sure where the SUMIF come in, but from what you posted, try something
like this:

For values in C1 and D1
E1: =C1*D1

Or to NOT display zero values
E1: =IF(COUNT(C1:D1)=2,C1*D1,"")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
Click to expand...
 
P

puiuluipui

I have one more problem...I use this vlookup formula in B cells
=IF(A2<>"",VLOOKUP($A2,$J$1:K51,2,FALSE),"") to extract some prices...

A B
1 01254 #N/A
2 total i.s. 12 3.65
3 total i.s. 15 2.86
4 02458 #N/A

I don't want to see #N/A ....I don't want anything to apear in B1, B4....
How can i do this??

Thanks again! My best regards
puiuluipui
 
F

flummi

The #NA means the value you are trying to lookup is not found in the
table you search in.
That could have two reasons:

1. it's not there, what do you then want to do about this?
2. you are looking in the wrong range because the K51 in your formula
is expanding the range down as you copy the formula down (or right if
you copy to the right)

Is your formula above taken from B1?
Do you really have "total i.s. 12" as a search criterion in your
pricing table?

To intercept the #NA message change your formula to:

=IF(A2<>"",If(ISNA(VLOOKUP($A2,$J$1:K51,2,FALSE)),0,VLOOKUP($A2,$J$1:K51,2,FALSE)),"")


Hans
 
Top