sumproduct question

[mark1]

I have three columns. They go like this:

100 1 2
200 0 2
100 1 0
200 0 2
100 1 2
200 0 0

I've always been under the impression that a + sign
represents OR. When I try this formula:

=sumproduct(((C1:C6>0)+(B1:B6>0))*A1:A6)

I get 900. I want it to equal 700, because I want the
formula to say if column B OR column C is greater than 0,
then sum column A. I must be doing something wrong. What
is it? Help is appreciated!!!

 

[Frank Kabel]

Hi
use
=sumproduct(--((C1:C6>0)+(B1:B6>0)>0),A1:A6)

 

[Bob Phillips]

Mark,

That is because the rows where both are >0 is double counting, that is you
get an extra 100 twice.

Try this

=SUMPRODUCT(--(((C1:C6>0)+(B1:B6>0))>0),A1:A6)

 

[mark1]

Hello Frank. Hope you are doing well today.

I understand that if you use the double-negative, a comma
can be substituted for your multiplication sign. What
about the addition sign? Is there a substitute for that?

 

[Frank Kabel]

Hi
no there's no similar substitution for this.

 

[Bob Phillips]

The double unary is not what determines whether a comma or an asterisk can
be used. The comma is only used where there is not a need to coerce the
evaluated array to an array of 1/0. You can use an asterisk here, but that
means that the whole formula is evaluated to a single array before the
SUMPRODUCT kicks in, as against there being 2 (or more) arrays if you use
the comma.

You can get an explanation at

http://www.xldynamic.com/source/xld.SUMPRODUCT.html