Or Function vs (Condition a+ Logical b)

[aes89]

Hello,

I want to achieve a criterion whereby the row/record is counted if Condition A is met and (any of condition 2 and 3 is met). Was just wondering why the below two formulas are not the same? I checked my answer with a pivot table and it gives me the same answer as formula 1)

Range A and B are of the same size
Condition 1 = Range A = X
Condition 2 = Range B = Y
Condition 3 = Range B = ""

1) =Sum(If((Condition 1)*(Condition 2 + Condition 3)=1,1,0)) entered as an array

2) =Sum(If((Condition 1)*Or(Condition 2,Condition 3)=1,1,0)) entered as an array

Thanks guys!

 

[Andrew Poulsom]

You can't use OR in an array formula. Use + for OR and * for AND.

 

[Jonmo1]

The proper syntax should be

=Sum(If((Condition 1)*((Condition 2) + (Condition 3)>0),1,0))

doing it like
(Condition 2 + Condition 3)=1
Will fail if both conditions are true, 1+1=2

 

[aes89]

Thanks guys! It makes complete sense to me now.

 

[pgc01]

Hi

Also:


=Sum((Condition 1)*((Condition 2) + (Condition 3)>0))

confirmed with CSE, or

=SumProduct((Condition 1)*((Condition 2) + (Condition 3)>0))

confirmed with Enter