CODEWITHSUNDEEP
excelmatrix
Stat Tistician
Stat Tistician

Reputation: 883

Creating a mathematical "or" in array function?

I have the following information given:

Airplane Typ Destination
Typ A         LAX
Typ B         LAX
Typ A         NEW
Typ A         NEW
Typ B         NEW
Typ C         ROS
Typ D         MUI

Now I want to have the total number of flights which go to NEW with airplane typ A. So I use the formula:

=SUM((A1:A5)='Typ A')*(B1:B5='NEW))

and I close this with strg shift enter so it gives a matrix formula. This works and I get the result 2. Now what do I have to enter, if I want the number of flights which use the Airplane Typ A or go to NEW? The result should be 4 of course.

Upvotes: 0

Views: 38

Answers (1)

user4039065
user4039065

Reputation:

Your existing formula would be better written as a standard formula instead of an array formula.

=COUNTIFS(A2:A6, "Typ A", B2:B6, "NEW")

The COUNTIFS function does not really process an OR condition but you can stack them together or use SUMPRODUCT function for that. The COUNTIFS is much more efficient than SUMPRODUCT.

=COUNTIF(A2:A6, "Typ A")+COUNTIF(B2:B6, "new")-COUNTIFS(A2:A6, "Typ A", B2:B6, "NEW")

Equivalent SUMPRODUCT would be,

=SUMPRODUCT(--(((A2:A6="Typ A")+(B2:B6="new"))>0))

Upvotes: 2

Related Questions