Need Help - #VALUE error using SUMPRODUCT

[MSC Bobs]

Hello Everyone,

I am receiving a #VALUE error in a SUMPRODUCT formula that I have used before, so I am not certain why it is not working now.

The old spreadsheet where it worked correctly became corrupted, and so I recreated the worksheet in a new workbook. I copied and pasted the data as values, but I recreated all of the formulas from scratch. I'm about 99% certain I constructed the new formula correctly; SUMPRODUCT is one of my Top 10 formulas I use.

I have searched the Web and the forums here for solutions, but I have not been able to fix my problem.

I am hoping somebody here can assist in resolving this issue. I've uploaded the file. Where I want to put the SUMPRODUCT in question is in cell D4 of the Calculations tab. It is drawing from the Stock.Detail data table in the Data tab. I've already typed the formula in the desired location so you can see what I did. This formula is exactly as it appeared in the old spreadsheet when it worked.

Thanks to those who can help, I really appreciate it.

 

[Luke M]

Problem is with data source, not the formula. Go to your Data sheet, Stock table. At end of Disco column, there are a few cells with the text string "-" in them. When the SUMPRODUCT tries to do math on a cell with text, it crashes. Delete/clear those cells, and all the formulas work again.

 

[Smallman]

Hi MSC Bobs

Very happy welcome to the chandoo forum.

Sumproduct is one of my favourite formulas in Excel, even though Sumifs has moved in I still prefer it.

The Stocks table has text in it were there should be numbers. Sumproduct is a very tempremental beast. When you remove the text the file works OK. It is an important rule to remember - put numbers where numbers should be and text where text should be and don't mix the two.

Here is the corrected file.

Take care

Smallman

 

[MSC Bobs]

:embarrassed emoticon:

Thank you for the quick response. Those were the only four cells I did not check to see if there were hidden text values.