Simple SUM array formula doesn't work. Bug?

[Zahum]

Problem.PNG

So basically, with this formula I want to SUM amounts if it meets criteria either Office="Oregon" OR difference<0. If I use OR function in single cell reference formula and copy down, Everything works fine, although if I try apply array formula, it doesn't work. I have found out, that if I use IF function in array formula instead, suddenly I get the correct answer. Does someone know, why array formula doesn't work, when I used OR?

Thank you.

[AliGW]

Why do you need an array formula? Can't you use SUMIFS?

[Zahum]

Why do you need an array formula? Can't you use SUMIFS?

Of course I could, but that's not the point. I was wondering why it doesn't work, as it clearly should

[Special-K]

SUMPRODUCT if you want OR

[Special-K]

Can you post this file?

[Zahum]

Can you post this file?

Sure, here it is.

[xlnitwit]

Hi,

OR will only return one True/False value and not the array of values you would require for your SUM formula to calculate as you want.

[shg]

Does someone know why array formula doesn't work, when I used OR?

Because OR and other logical functions return a single result, not an array of results.

[Special-K]

If you change all the names to ranges and use INDIRECT,
e.g.
in G12
=SUM(OR(INDIRECT("B2:B"&H14)="Oregon",INDIRECT("f2:f"&H14)<0)*INDIRECT("c2:c"&H14))
and use H14 as an manual index you can see how its adding up individually

even though columns G and H have 0 the amount in column C is still being summed, hence the difference of 3315 (C7, C10, C11)

[Zahum]

I see! I didn't know about this. Thank you!

[Tony Valko]

Try this...

=SUMPRODUCT(SIGN((Office="Oregon")+(Difference<0)),Amount)