if statements in sumproduct formula

D

Doug Glancy

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<>"",rngSource=$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
 
B

Bob Phillips

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="")*(rngSource<>"")))*rngAmount)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Doug Glancy said:
I have the following formula:
Click to expand...
=SUMPRODUCT((IF($G$5<>"",rngDate>=$G$5,rngDate<>""))*(IF($H$5<>"",rngDate<=$
 
D

Doug Glancy

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
 
B

Bob Phillips

Doug,

The addition, or plus sign, denotes an OR to SP, just as * denotes AND.

Bob
 
Top