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("CI RCLE",$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.
--
Walter Briscoe