"OR" Function in an Array Formula

[2sherberts]

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),GrossMargin!BF$29:BF$89,0))}


Thanks,

Sherry

 

[Bob Phillips]

=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)

 

[David Biddulph]

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).

 

[2sherberts]

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