1

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

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

See also

https://stackoverflow.com/questions/20238816/using-sumifs-with-multiple-and-or-conditions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.