Hi,

Lets assume that you have the folowing values in first five rows of columns
A and B.
Col A;Col B
value1;value2
value3;value2
value1;value3
value3;value3

Then lets evaluate the following arrayformulas.
{=OR($A$2:$A$5=value1)} equals {=OR({t,f,t,f})}
resulting in {t}

{=OR($A$2:$A$5=value1)} equals {=OR({t,t,f,f})}
resulting in {t}

then
{=OR(($A$2:$A$5=value1);($B$2:$B$5=value2))}
equals
{=OR({t,f,t,f};{t,t,f,f})}
equals
{=OR({t};{t})}
resulting in {t}

hence,
{=IF(OR(($A$2:$A$5=value1);($B$2:$B$5=value2));$C$ 2:$C$5;"")}
will always be true if there is at least one value1 in column A, or at least
one value2 in
column B. If there is no value1 in column A, and no Value2 in columnB the
resulting array from
the "IF" function will be {"","","",""}, and =SUM({"","","",""}) results in
#VALUE.

rgds,
Lazzzx



"Tom" skrev i meddelelsen
...
Assuming that in A2:C5 is a table of data,
Why is it so that these two worksheet array formulas give different
results:
1) {=SUM(IF(OR(($A$2:$A$5=value1);($B$2:$B$5=value2)) ;$C$2:$C$5;""))}
2) {=SUM(IF(($A$2:$A$5=value1)+($B$2:$B$5=value2);$C$ 2:$C$5;""))}
and only the second one gets correct result (summing C2:C5 only if
A2:A5 has the value of value1 or B2:B5 has the value of value2)
I'm not sure but it looks like the logical function OR in the array
form
does not translate to na array {t,f,f,t....} but to a single {t|f}
value, but
(($A$2:$A$5=value1)+($B$2:$B$5=value2)) translates to an array of
{t,f,f,t...}. Am I right ? Why is it so ?

Thanks
Tom