Going bonkers over #VALUE! and blank cells

[medexcel]

Hi, say I have a spreadsheet that calculates the price of each item sold for a number of shops. In each column are the number sold per shop, with the top line being the price per item.
---A------------B--------------C----------------D--------------E
1-------------total-------apples---------oranges-------bananas
2---------------price----------$1--------------$2----------$1.50
3-shop 1--------$13-----------3----------------5
4-shop 2----------$9-----------2----------------2---------------2
5-shop 3-----------$0


Of course the formula for giving the value $13 in B3 is
= (C$1*C3)+(D41*D3)+(E*1*E3) dragged down column B.

So far so good, note some cells are blank as nothing was sold, and the formula gives a zero for those items.

I just added column F for pears and now every line that has one or more blank cells gives a #VALUE! in column B. Why has this suddenly started happening, and how I can get the thing to work again? I have not changed the formula at all, yet now even if I strip out eveything and just go = (C$1*C3), if C3 doesn't contain a value then B3 gives VALUE! Yet previously with the formula = (C$1*C3), if C3 was empty then B3 would say 0. Am I missing something really obvious? I just need = (C$1*C3) to give me 0 where C3 is blank. (Actually I'm copying the values for the shops from another spreadsheet and there are actually several more columns and many more rows, so a solution saying if the cell is blank return 0 isn't really an option.....).

 

[MrKowz]

Try using a SUMPRODUCT:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Total</td><td style=";">Apples</td><td style=";">Oranges</td><td style=";">Bananas</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Price</td><td style="text-align: right;;">$1.00</td><td style="text-align: right;;">$2.00</td><td style="text-align: right;;">$1.50</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Shop 1</td><td style="text-align: right;;">$13.00</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Shop 2</td><td style="text-align: right;;">$9.00</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Shop 3</td><td style="text-align: right;;">$13.00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">$C$2:$E$2,C3:E3</font>)</td></tr></tbody></table></td></tr></table><br />

 

[mole999]

Added column F, did you insert it and move everything on the right to the right by 1 ?

 

[medexcel]

Thanks, but still no joy. I just cannot see what is different in the two versions of the spreadsheet. In desperation I copied the same first worksheet into the second worksheet and tried again adding the extra column - initially it worked but after about four rows I started getting VALUE! errors again for no apparent reason. It works for the first few rows, and I just copied and pasted the same cell values into each subsequent rows and after about four rows down the VALUE! error returns. Bizarre......