Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct value error | Excel Discussion (Misc queries) | |||
error sumproduct #value! | Excel Discussion (Misc queries) | |||
error in SUMPRODUCT | Excel Discussion (Misc queries) | |||
SUMPRODUCT #VALUE! error | Excel Worksheet Functions | |||
SUMPRODUCT ERROR | Excel Discussion (Misc queries) |