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

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

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

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

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

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