# How to aggregate two OR criteria in same sumif

The formula below is the same other than the last criteria: in the first it should equal "IN BETWEEN" and in the second it should equal "BUSY".

``````=SUMIFS('2016/09/26'!\$S\$3:\$S,'2016/09/26'!\$A\$3:\$A,H3,'2016/09/26'!\$C\$3:\$C,"IN BETWEEN")+
SUMIFS('2016/09/26'!\$S\$3:\$S,'2016/09/26'!\$A\$3:\$A,H3,'2016/09/26'!\$C\$3:\$C,"BUSY")
``````

I wanted to aggregate both in the same formula with something like an OR condition but couldn't get it to work as I don't know the syntax. What I tried throws a "Formula Parse" error:

``````=SUMIFS('2016/09/26'!\$S\$3:\$S,'2016/09/26'!\$A\$3:\$A,A3,'2016/09/26'!\$C\$3:\$C,
OR(="IN BETWEEN",="BUSY"))
``````

The right way to write OR arguments is `OR(A1="string1",A1="string2")` but OR doesn't allow arrays as arguments.

As Aʟ E. pointed out, there is a question related to the core or the question on this site about that is specific for AND conditions and this question is about a combination of AND and OR conditions.

One alternative is to take advantage that boolean values, TRUE and FALSE are interpreted as 1 and 0 respectively when arithmetic operations are applied to them.

• A multiplication of TRUE/FALSE is equivalent to AND.
• A sum of TRUE/FALSE of mutually excluded comparisons is directly equivalent to OR.

See the following pattern

``````=SUMPRODUCT(A1:A6,(B1:B6="A")*((C1:C6="B")+(C1:C6="C")))
``````

Just replace the ranges an values with those of your specific problem. The result is the following:

``````=SUMPRODUCT('2016/09/26'!\$S\$3:\$S,
('2016/09/26'!\$A\$3:\$A=H3)*
(('2016/09/26'!\$C\$3:\$C="IN BETWEEN")+('2016/09/26'!\$C\$3:\$C="BUSY"))
)
``````