Creating a mathematical "or" in array function?

Question

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.

Try the COUNTIF function or COUNTIFS function. BTW, the term you should be using is array formula, not matrix formula. — user4039065
– user4039065, Commented Mar 15, 2015 at 18:54
Of course I can? When I enter this formula and close it with strg shift and enter it works fine? — Stat Tistician
– Stat Tistician, Commented Mar 15, 2015 at 18:56
Yes ok thanks, I also now that for the and conditions I could use countifs function. So =countifs(A1:A5;"=Typ A"; B1:B5;"=NEW"). But what if the logical condition is a "or"? — Stat Tistician
– Stat Tistician, Commented Mar 15, 2015 at 19:08

user4039065 · Accepted Answer · 2015-03-15 19:09:27Z

2

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))

answered Mar 15, 2015 at 19:09

user4039065

Sign up to request clarification or add additional context in comments.

4 Comments

Stat Tistician Over a year ago

Thanks! But what does the "--" in the formula at the beginning stand for?

user4039065 Over a year ago

Maybe you could google or bing excel double unary or excel double minus ...?

Stat Tistician Over a year ago

Can the "--" be avoided somehow?

user4039065 Over a year ago

No. You cannot add TRUE+TRUE. But by using a mathematical operation on the booleans, you convert them to 1+1. Your original formula performs this task by multiplying the two booleans against each other. Try =SUM(((A1:A5)="Typ A")). It won't work. But try =SUM(--((A1:A5)="Typ A")) and it will.

Collectives™ on Stack Overflow

Creating a mathematical "or" in array function?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related