Add another Condition Column to Sumproduct formula

[bdb1974]

This is my working formula

Please Login or Register  to view this content.

I want to make it an OR condition by having $R$3:$R$28=88676 as another option. I've tried the following formula.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I'm getting a value error and can't figure out where the problem is.

Any help is appreciated.

Thanks,
BDB

[JBeaucaire]

Can't fathom your actual data, but if it's just an extension you want, this seem to be usable:

=SUMPRODUCT(--($R$3:$R$28=88398)--($R$3:$R$28=88397),N$3:N$28,$P$3:$P$28)

By leaving out the comma and adding a second criteria, it acts like an AND.

[bdb1974]

Yes, That's exactly what I needed with a little cell referencing corrections.

Please Login or Register  to view this content.

Thanks,

BDB

[DonkeyOte]

Glad you have resolved - the more traditional syntax for an OR would be along the lines of:

=SUMPRODUCT(($R$3:$R$30=88676)+($S$3:$S$30=88676),O$3:O$30,$P$3:$P$30)

I only highlight this in case you come across other examples.

The first double unary in JB's examples could be disposed of as the second -- acts as a + and thus coerces both Boolean arrays.

It's worth pointing out though that if both R & S = 88676 you will be double counting the result of O*P.

[bdb1974]

Thanks Donkey Ote for sharing your knowledge.

Yes, the following is true for both examples

Please Login or Register  to view this content.

Is there a way to write the formula so having 2 of the same values will not cause it to double count. Just Curious. - BDB

[DonkeyOte]

There will definitely be better methods but I'm struggling today if truth be told...

=SUMPRODUCT(--(ISNUMBER(SEARCH(":88676:",":"&$R$3:$R$30&":"&$S$3:$S$30&":"))),O$3:O$30,$P$3:$P$30)

[romperstomper]

Or:
=SUMPRODUCT(SIGN(($R$3:$R$30=88676)+($S$3:$S$30=88676)),O$3:O$30,$P$3:$P$30)

[DonkeyOte]

much more elegant (& efficient)... thanks R.

[romperstomper]

I'm having one of those days where I have flashes of competence!