"OR" Function in an Array Formula
 

Hi,

Can someone tell me what is wrong with my formula? It seems that the
"OR" function in this formula is not working. It is bringing back a
zero when there should be a number.


{=SUM(IF((GrossMargin!$B$29:$B$89=Metrics!$B35)*($ C35=GrossMargin!$C
$29:$C$89)+($D35=GrossMargin!$C$29:$C$89),GrossMar gin!BF$29:BF$89,0))}


Thanks,

Sherry

"OR" Function in an Array Formula
 

=SUM(IF((GrossMargin!$B$29:$B$89=Metrics!$B35)*(($ C35=GrossMargin!$C$29:$C$89)+($D35=GrossMargin!$C$ 29:$C$89)),GrossMargin!BF$29:BF$89,0))

shouldn't your tests against C35 and D35 also be testing in sheet Metrics?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
...
Hi,

Can someone tell me what is wrong with my formula? It seems that the
"OR" function in this formula is not working. It is bringing back a
zero when there should be a number.


{=SUM(IF((GrossMargin!$B$29:$B$89=Metrics!$B35)*($ C35=GrossMargin!$C
$29:$C$89)+($D35=GrossMargin!$C$29:$C$89),GrossMar gin!BF$29:BF$89,0))}


Thanks,

Sherry

"OR" Function in an Array Formula
 

Bob,

Your version changes it from
(B condition AND C condition) OR D condition
to
B condition AND (C condition OR D condition)

The OP was complaining at getting a zero where something non-zero was
expected, so doesn't it seem unlikely that your change was what was wanted
to cure the reported symptoms, (as it doesn't change any FALSE conditions to
TRUE, but changes TRUE to FALSE for the two cases where B is FALSE and D is
TRUE)?

If they'd expected a zero but got something non-zero, yours was certainly a
possibility, but of course it would have been handy if the OP had clarified
the requirements.

Your final sentence sounds more likely to be the point (and if they then get
something unexpected, your first point may then come into play).
--
David Biddulph

"Bob Phillips" wrote in message
...
=SUM(IF((GrossMargin!$B$29:$B$89=Metrics!$B35)*(($ C35=GrossMargin!$C$29:$C$89)+($D35=GrossMargin!$C$ 29:$C$89)),GrossMargin!BF$29:BF$89,0))

shouldn't your tests against C35 and D35 also be testing in sheet Metrics?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message
...
Hi,

Can someone tell me what is wrong with my formula? It seems that the
"OR" function in this formula is not working. It is bringing back a
zero when there should be a number.


{=SUM(IF((GrossMargin!$B$29:$B$89=Metrics!$B35)*($ C35=GrossMargin!$C
$29:$C$89)+($D35=GrossMargin!$C$29:$C$89),GrossMar gin!BF$29:BF$89,0))}


Thanks,

Sherry

"OR" Function in an Array Formula
 

Thank you for responding to my post. Both reply helped a lot!