#VALUE! error

[exceluserkt]

I have a table that contains SUMPRODUCT functions. It compares text values and then sums a corresponding number value. It was working fine until I upgraded to 2007 and now when I hit F9(Calculate), all the cells go to #VALUE! and I can't get the actual number values back. How do I get the values back?

Multiple people in my office rely on this sheet so please help

 

[Scott Huish]

What is your formula? Do you have #VALUE! errors anywhere in your data range?

 

[exceluserkt]

The first formula in the table is

=SUMPRODUCT(--(LaborHours!$A$6:$A$4999=$A3),--(LaborHours!$D$6:$D$4999=J$2),--(LaborHours!$E$6:$E$4999))

Column A is a project number(text) column D is a category (text) and E is the number of hours to sum. The sheet where the numbers are summed does not contai any errors. This problem only occured when I upgraded to 2007 and hit F9(calculate). It had been working flawlessly for two months.

 

[Scott Huish]

That formula should still work. Although you don't need the -- and the parentheses around the last part.


Put this formula in a blank cell on the same sheet where you have that formula:

=SUMPRODUCT(--(ISERROR(LaborHours!A6:A4999)))+SUMPRODUCT(--(ISERROR(LaborHours!$D$6:$D$4999)))+SUMPRODUCT(--(ISERROR(LaborHours!$E$6:$E$4999)))+ISERROR(A3)+ISERROR(J2)

What does it return?

If that returns 0, is there any text in E6:E4999 on the LaborHours sheet?

 

[exceluserkt]

You were right. I found that someone entered a " . " in that column instead of " 0 "

Thanks for your help

 

[Aladin Akyurek]

You were right. I found that someone entered a " . " in that column instead of " 0 "

Thanks for your help

If such is in the range to sum, the native syntax would cope with that...

=SUMPRODUCT(
   --(LaborHours!$A$6:$A$4999=$A3),
   --(LaborHours!$D$6:$D$4999=J$2),
  LaborHours!$E$6:$E$4999)