SUMPRODUCT Error

C

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
 
D

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????
 
C

Curtis

Thanks




Dave Peterson said:
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????
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top