SUMPRODUCT Error
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 |
SUMPRODUCT Error
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 wrote: 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 |
SUMPRODUCT Error
Thanks
"Dave Peterson" wrote: 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 wrote: 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 |
All times are GMT +1. The time now is 07:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com