OR in SUMPRODUCT formula

[Walter Briscoe]

I recently read Bob Phillips famous paper on SUMPRUCT in
<http://www.xldynamic.com/source/xld.sumproduct.html>

It has a lot to say about AND operations using multiplication.
It has a little to say about OR operations using addition.

I need to use both. I have some difficulty in turning FALSE and TRUE
into numbers 0 and 1 - Yes I know TRUE is usually -1.

I have 'slices' of 3 rows:

Row R S T U V W X Y Z AA AB AC Column
19 0 26 18 0 18 0 33 0 0 0 0 0
34 0 0 33 24 0 0 0 0 0 0 0 0
35 0 15 34 25 0 0 0 0 0 0 0 0

I want to OR rows 34 and 35 and AND the result with row 19.
Rows 34 and 35 are fixed. 19 is copied through.

I currently have
=SUMPRODUCT(--(($R19:$AC19<>0)*(($R$34:$AC$34+($R$35:$AC$35))<>0))
which produces the result 2 as intended.

Can the formula be simplified?

34 and 35 OR'd together should be
R S T U V W X Y Z AA AB AC
34 OR 35 0 1 1 1 0 0 0 0 0 0 0 0

19 is ANDed
19 0 1 1 0 1 0 1 0 0 0 0 0

giving the result
34 OR 35 & 19 0 1 1 0 0 0 0 0 0 0 0 0

That 'row' contains one twice and I get the result 2 as intended.

I previously misanalysed my requirement.
I had multiplied the 3 'rows' to produce A and B and C in
=SUMPRODUCT((($R19:$AC19<>0)*(($R$34:$AC$34)<>0)*($R$35:$AC$35)<>0)))
That produces the answer 1 where 2 is correct.

P.S. I add another component to tweak that formula:
+IF(ISERROR(SEARCH("CIRCLE",$AD19)),0,N(SEARCH("CIRCLE",$AD19)<>0))
$AD19 can case-insensitively hold "Circle" or not - not includes $AD19 =
"" which causes SEARCH to give an error.
That formula excerpt is about twice as complicated as I would like it to
be. Any simplification suggestions?

I will explain my data.
Rows represent London Underground stations. Columns represent lines.
My A and (B or C) calculates the number of lines at A common to B or C.
My tweak is used to force an addition to the sumproduct which takes the
values:
0 The stations are not connected
1 One line connects the stations
2 etc. more than 1 line connects or should be considered to connect the
stations.

 

[joeu2004]

Yes I know TRUE is usually -1.

No. Excel TRUE is 1. VBA True is -1.

I want to OR rows 34 and 35 and AND the result with row 19.
Rows 34 and 35 are fixed. 19 is copied through.
I currently have
=SUMPRODUCT(--(($R19:$AC19<>0)*(($R$34:$AC$34+($R$35:$AC$35))<>0))

I think you want:

=SUMPRODUCT(($R19:$AC19<>0)*(($R$34:$AC$34<>0)+($R$35:$AC$35<>0)>0))

Some things to note:

1. When using "+" for OR in this context, generally you should test
the sum for ">0".

You can get away without ">0" only when the summed (ORed) conditions
are mutually exclusive. For example, (A1:A10="a") + (A1:A10="b").
Both conditions cannot be true simultaneously. But you need
((A1:A10="a")+(B1:B10="b")>0) because both conditions can be true,
resulting in a sum of 2, whereas we usually want 1 or 0.


2. You do not need double-negative (--) if you are using other
arithmetic operators.

The purpose of the double-negative is to convert TRUE and FALSE into 1
and 0, which SUMPRODUCT requires. But any arithmetic operation will
have the same result. For example, TRUE+TRUE is 2. No need to write
(--TRUE)+(--TRUE).

 

[Walter Briscoe]

In message <[email protected]
s.com> of Mon, 13 Jun 2011 08:22:33 in microsoft.public.excel.worksheet.

No. Excel TRUE is 1. VBA True is -1.

So much for my plans to divert side issues.
You're right and I was wrong after too much VBA. ;(

I think you want:

=SUMPRODUCT(($R19:$AC19<>0)*(($R$34:$AC$34<>0)+($R$35:$AC$35<>0)>0))

I agree that hits the spot and is simpler than my work.

Some things to note:

1. When using "+" for OR in this context, generally you should test
the sum for ">0".

You can get away without ">0" only when the summed (ORed) conditions
are mutually exclusive. For example, (A1:A10="a") + (A1:A10="b").
Both conditions cannot be true simultaneously. But you need
((A1:A10="a")+(B1:B10="b")>0) because both conditions can be true,
resulting in a sum of 2, whereas we usually want 1 or 0.

I had that problem. I first was trying things like
OR(A1:A10="a",B1:B10="b"). I was surprised this produced a single
FALSE/TRUE result (i.e.. a scalar) rather than an array of FALSE/TRUE
values.

2. You do not need double-negative (--) if you are using other
arithmetic operators.

I like that rule of thumb.

The purpose of the double-negative is to convert TRUE and FALSE into 1
and 0, which SUMPRODUCT requires. But any arithmetic operation will
have the same result. For example, TRUE+TRUE is 2. No need to write
(--TRUE)+(--TRUE).

I understood that to some extent after reading
<http://www.mcgimpsey.com/excel/formulae/doubleneg.html>

Many thanks for the help. I should find my next sumproduct operation
easier as a result of that help.

 

[joeu2004]

=SUMPRODUCT(($R19:$AC19<>0)*(($R$34:$AC$34<>0)+($R$35:$AC$35<>0)>0)) [....]
1. When using "+" for OR in this context, generally you
should test the sum for ">0".

Why? I prefer 0 as a synonym for FALSE and think of TRUE as <> 0.

This isn't about preference; it's about correctness. I guess my
explanation in the subsequent paragraph was not clear.

If both $R$34:$AC$34<>0 and $R$35:$AC$35<>0 are true, then ($R$34:$AC
$34<>0)+($R$35:$AC$35<>0) is 2, not 1.

If $R19:$AC19<>0 is also true, then ($R19:$AC19<>0)*(($R$34:$AC$34<>0)+
($R$35:$AC$35<>0)) is 2, not 1.

Thus, SUMPRODUCT would double-account (2 instead of 1) for the one
condition.

I assumed that you want to count only once when "x AND (y OR z)" is
true.

However, if you want to count "x AND y" and "x AND z" separately, then
the ">0" should indeed be omitted.