Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct syntax - more complex
I have a more complex sumproduct syntax problem
=SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),--(OR((Tasks!CJ1:CJ900="New"),(Tasks!CJ1:CJ900="In Progress"),(Tasks!CJ1:CJ900="In Progress - New"))),Tasks!CG1:CG900) The equation worked fine, then I added the OR statement. The values for the OR statement are all false, so the OR statement should be false. This means the sumproduct should resolve to a 0, instead I am resolving to a #VALUE. Where have I made an error? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct syntax - more complex
=SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),
--((Tasks!CJ1:CJ900="New")+(Tasks!CJ1:CJ900="In Progress")+(Tasks!CJ1:CJ900="In Progress - New")),Tasks!CG1:CG900) or =SUMPRODUCT((Tasks!CI1:CI900="dduplessis")*(Tasks! N1:N900<"CUT")* ((Tasks!CJ1:CJ900={"New","In Progress","In Progress - New"}))*Tasks!CG1:CG900) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mitchell" wrote in message ... I have a more complex sumproduct syntax problem =SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),--(OR( (Tasks!CJ1:CJ900="New"),(Tasks!CJ1:CJ900="In Progress"),(Tasks!CJ1:CJ900="In Progress - New"))),Tasks!CG1:CG900) The equation worked fine, then I added the OR statement. The values for the OR statement are all false, so the OR statement should be false. This means the sumproduct should resolve to a 0, instead I am resolving to a #VALUE. Where have I made an error? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct syntax - more complex
Try...
=SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),(Ta sks!CJ1:CJ900="New")+(Tasks!CJ1:CJ900="In Progress")+(Tasks!CJ1:CJ900="In Progress - New"),Tasks!CG1:CG900) Hope this helps! In article , Mitchell wrote: I have a more complex sumproduct syntax problem =SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),--(OR((T asks!CJ1:CJ900="New"),(Tasks!CJ1:CJ900="In Progress"),(Tasks!CJ1:CJ900="In Progress - New"))),Tasks!CG1:CG900) The equation worked fine, then I added the OR statement. The values for the OR statement are all false, so the OR statement should be false. This means the sumproduct should resolve to a 0, instead I am resolving to a #VALUE. Where have I made an error? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct syntax - more complex
Actually, I believe the following is more efficient...
=SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),--I SNUMBER(MATCH(Tasks!CJ1:CJ900,{"New","In Progress","In Progress - New"},0)),Tasks!CG1:CG900) or =SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),--I SNUMBER(MATCH(Tasks!CJ1:CJ900,A2:A4,0)),Tasks!CG1: CG900) ....where A2:A4 contains New, In Progress, and In Progress - New. Hope this helps! In article , Domenic wrote: Try... =SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),(Ta sks!CJ1:CJ900="New")+(Tasks!CJ1:CJ900="In Progress")+(Tasks!CJ1:CJ900="In Progress - New"),Tasks!CG1:CG900) Hope this helps! In article , Mitchell wrote: I have a more complex sumproduct syntax problem =SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),--(OR( (T asks!CJ1:CJ900="New"),(Tasks!CJ1:CJ900="In Progress"),(Tasks!CJ1:CJ900="In Progress - New"))),Tasks!CG1:CG900) The equation worked fine, then I added the OR statement. The values for the OR statement are all false, so the OR statement should be false. This means the sumproduct should resolve to a 0, instead I am resolving to a #VALUE. Where have I made an error? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct syntax - more complex
Thanks, it worked perfectly
"Bob Phillips" wrote: =SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"), --((Tasks!CJ1:CJ900="New")+(Tasks!CJ1:CJ900="In Progress")+(Tasks!CJ1:CJ900="In Progress - New")),Tasks!CG1:CG900) or =SUMPRODUCT((Tasks!CI1:CI900="dduplessis")*(Tasks! N1:N900<"CUT")* ((Tasks!CJ1:CJ900={"New","In Progress","In Progress - New"}))*Tasks!CG1:CG900) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Mitchell" wrote in message ... I have a more complex sumproduct syntax problem =SUMPRODUCT(--(Tasks!CI1:CI900="dduplessis"),--(Tasks!N1:N900<"CUT"),--(OR( (Tasks!CJ1:CJ900="New"),(Tasks!CJ1:CJ900="In Progress"),(Tasks!CJ1:CJ900="In Progress - New"))),Tasks!CG1:CG900) The equation worked fine, then I added the OR statement. The values for the OR statement are all false, so the OR statement should be false. This means the sumproduct should resolve to a 0, instead I am resolving to a #VALUE. Where have I made an error? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT as array formula? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Help with Complex SUMPRODUCT formula | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |