Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements in sumproduct formula
I have the following formula:
=SUMPRODUCT((IF($G$5<"",rngDate=$G$5,rngDate<"" ))*(IF($H$5<"",rngDate<=$H$5,rngDate<""))*(IF($I $5<"",rngUse=$I$5,rngUse<""))*(IF($J$5<"",rngSo urce=$J$5,rngSource<""))*rngAmount) It evaluates to 0, which is not what I expect. If I go through with F9 and on each If statement, I get a series of {TRUE, FALSE, TRUE} as expected and if I then hit Enter, the formula evaluates to the amount I expect. I thought maybe the formula was too long, but I get the same result if I include only two sets of conditions. Thanks, Doug Glancy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements in sumproduct formula
Why not just use
=SUMPRODUCT(((($G$5<"")*(rngDate=$G$5))+(($G$5=" ")*(rngDate<"")))* ((($H$5<"")*(rngDate<=$H$5))+(($H$5="")*(rngDate< "")))* ((($I$5<"")*(rngUse=$I$5))+(($I$5="")*(rngUse<"" )))* ((($J$5<"")*(rngSource=$J$5))+(($J$5="")*(rngSour ce<"")))*rngAmount) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doug Glancy" wrote in message ... I have the following formula: =SUMPRODUCT((IF($G$5<"",rngDate=$G$5,rngDate<"" ))*(IF($H$5<"",rngDate<=$ H$5,rngDate<""))*(IF($I$5<"",rngUse=$I$5,rngUse< ""))*(IF($J$5<"",rngSour ce=$J$5,rngSource<""))*rngAmount) It evaluates to 0, which is not what I expect. If I go through with F9 and on each If statement, I get a series of {TRUE, FALSE, TRUE} as expected and if I then hit Enter, the formula evaluates to the amount I expect. I thought maybe the formula was too long, but I get the same result if I include only two sets of conditions. Thanks, Doug Glancy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements in sumproduct formula
Bob,
Because I'm not as smart as you? Thanks, that works. Doug "Bob Phillips" wrote in message ... Why not just use =SUMPRODUCT(((($G$5<"")*(rngDate=$G$5))+(($G$5=" ")*(rngDate<"")))* ((($H$5<"")*(rngDate<=$H$5))+(($H$5="")*(rngDate< "")))* ((($I$5<"")*(rngUse=$I$5))+(($I$5="")*(rngUse<"" )))* ((($J$5<"")*(rngSource=$J$5))+(($J$5="")*(rngSour ce<"")))*rngAmount) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doug Glancy" wrote in message ... I have the following formula: =SUMPRODUCT((IF($G$5<"",rngDate=$G$5,rngDate<"" ))*(IF($H$5<"",rngDate<=$ H$5,rngDate<""))*(IF($I$5<"",rngUse=$I$5,rngUse< ""))*(IF($J$5<"",rngSour ce=$J$5,rngSource<""))*rngAmount) It evaluates to 0, which is not what I expect. If I go through with F9 and on each If statement, I get a series of {TRUE, FALSE, TRUE} as expected and if I then hit Enter, the formula evaluates to the amount I expect. I thought maybe the formula was too long, but I get the same result if I include only two sets of conditions. Thanks, Doug Glancy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements in sumproduct formula
My 'just' was referring to not being smart with embedded IFs <BG
Bob "Doug Glancy" wrote in message ... Bob, Because I'm not as smart as you? Thanks, that works. Doug "Bob Phillips" wrote in message ... Why not just use =SUMPRODUCT(((($G$5<"")*(rngDate=$G$5))+(($G$5=" ")*(rngDate<"")))* ((($H$5<"")*(rngDate<=$H$5))+(($H$5="")*(rngDate< "")))* ((($I$5<"")*(rngUse=$I$5))+(($I$5="")*(rngUse<"" )))* ((($J$5<"")*(rngSource=$J$5))+(($J$5="")*(rngSour ce<"")))*rngAmount) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doug Glancy" wrote in message ... I have the following formula: =SUMPRODUCT((IF($G$5<"",rngDate=$G$5,rngDate<"" ))*(IF($H$5<"",rngDate<=$ H$5,rngDate<""))*(IF($I$5<"",rngUse=$I$5,rngUse< ""))*(IF($J$5<"",rngSour ce=$J$5,rngSource<""))*rngAmount) It evaluates to 0, which is not what I expect. If I go through with F9 and on each If statement, I get a series of {TRUE, FALSE, TRUE} as expected and if I then hit Enter, the formula evaluates to the amount I expect. I thought maybe the formula was too long, but I get the same result if I include only two sets of conditions. Thanks, Doug Glancy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements in sumproduct formula
Bob,
My response was an attempt at a witty balance of humor and appreciation <g. I had never seen it done the way you have here, with addition inside the Sumproduct, so I learned something new. Thanks for all your help in these groups, and for your quick response to my question. Doug "Bob Phillips" wrote in message ... My 'just' was referring to not being smart with embedded IFs <BG Bob "Doug Glancy" wrote in message ... Bob, Because I'm not as smart as you? Thanks, that works. Doug "Bob Phillips" wrote in message ... Why not just use =SUMPRODUCT(((($G$5<"")*(rngDate=$G$5))+(($G$5=" ")*(rngDate<"")))* ((($H$5<"")*(rngDate<=$H$5))+(($H$5="")*(rngDate< "")))* ((($I$5<"")*(rngUse=$I$5))+(($I$5="")*(rngUse<"" )))* ((($J$5<"")*(rngSource=$J$5))+(($J$5="")*(rngSour ce<"")))*rngAmount) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doug Glancy" wrote in message ... I have the following formula: =SUMPRODUCT((IF($G$5<"",rngDate=$G$5,rngDate<"" ))*(IF($H$5<"",rngDate<=$ H$5,rngDate<""))*(IF($I$5<"",rngUse=$I$5,rngUse< ""))*(IF($J$5<"",rngSour ce=$J$5,rngSource<""))*rngAmount) It evaluates to 0, which is not what I expect. If I go through with F9 and on each If statement, I get a series of {TRUE, FALSE, TRUE} as expected and if I then hit Enter, the formula evaluates to the amount I expect. I thought maybe the formula was too long, but I get the same result if I include only two sets of conditions. Thanks, Doug Glancy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements in sumproduct formula
Doug,
The addition, or plus sign, denotes an OR to SP, just as * denotes AND. Bob "Doug Glancy" wrote in message ... Bob, My response was an attempt at a witty balance of humor and appreciation <g. I had never seen it done the way you have here, with addition inside the Sumproduct, so I learned something new. Thanks for all your help in these groups, and for your quick response to my question. Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Array formula and multiplying conditions | Excel Worksheet Functions | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Shorten sumproduct formula | Excel Discussion (Misc queries) | |||
adding two sumproduct formulas together | Excel Worksheet Functions |