SUMPRODUCT Error

[Curtis]

When I use the following formula I get the correct results

=SUMPRODUCT(--('Employee Data'!$H$4:$H$60=$C$3)*--('Employee
Data'!$J$4:$J$60=$A19)*--('Employee Data'!$P$4:$U$60))

However when I change the last range P4:u60 to another range say x4:ac60 I
get the #VALUE! error.

The only difference is that the first range the values are manully recorded
and the second range the numbers are procdued by the following Formula

=IF(P55="","",VLOOKUP(P$2,Points!$B$2:$D$7,3)*P55)

Any asssitance would be appreciated

 

[Dave Peterson]

First, drop the --'s from your formula:

=SUMPRODUCT(('Employee Data'!$H$4:$H$60=$C$3)
*('Employee Data'!$J$4:$J$60=$A19)
*('Employee Data'!$P$4:$U$60))

Second, if you have text in that last range (P4:U60), then it's the equivalent
of doing:

=3*""
or
=3*"asdf"
And that'll end up with the same kind of #value! error.

If you have formulas in that third range that return ""'s, maybe you could
return 0's and format those cells so that they look empty????

 

[Curtis]

Thanks

First, drop the --'s from your formula:

=SUMPRODUCT(('Employee Data'!$H$4:$H$60=$C$3)
*('Employee Data'!$J$4:$J$60=$A19)
*('Employee Data'!$P$4:$U$60))

Second, if you have text in that last range (P4:U60), then it's the equivalent
of doing:

=3*""
or
=3*"asdf"
And that'll end up with the same kind of #value! error.

If you have formulas in that third range that return ""'s, maybe you could
return 0's and format those cells so that they look empty????