sumproduct for blank values

All,

Can you please help me solve the below problem.

I have two sheets in my excel workbook. One sheet has materials and total column and other sheet has material and price broken down by periods. I am using a sumproduct in sheet1 to do a match of material in sheet2 and add all its corresponding values and display in sheet1. the problem is that if the material value in sheet2 is blank then i want blank to be displayed in sheet1 but instead its showing 0. Is there any way i can overcome this problem. Please look at the attached sheet for more details.

In sheet1 A4 i want a blank value

example3.xls

You only really need SUMIF for a single criterion, i.e. in A4 copied down

=SUMIF(Sheet2!A$4:A$10,A4,Sheet2!C$4:C$10)

I'd just format the cell to not show zeroes, i.e. custom format as

0;;

If you want to do that within the formula you could change to

=IF(SUMIF(Sheet2!A$4:A$10,A4,Sheet2!C$4:C$10),SUMIF(Sheet2!A$4:A$10,A4,Sheet2!C$4:C$10),"")

I can't see the reply

Thanks for your reply, but I can't format the cells to not show 0's because, for some materials i will have 0 value and that need to showup and for other materials the value could be blank, in which case i would need to see a blank value

I used the second formula and its working.Thank you so much for your help.

I used the second formula and its working but it is eliminating 0 value also. If a material has 0 price i was zero in sheet1 total, but if the material has blank values the toal in sheet1 should be blank. I don't want to eliminate 0 value. Can this be acheived?

I used the second formula and its working but it is eliminating 0 value also. If a material has 0 price i was zero in sheet1 total, but if the material has blank values the toal in sheet1 should be blank. I don't want to eliminate 0 value. Can this be acheived?