On Jun 13, 7:06*am, Walter Briscoe
wrote:
Yes I know TRUE is usually -1.

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


Walter wrote:
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).