COUNTIFS with multiple criteria and OR logic

An excel formula to countifs with multiple criteria and or logic

Example

Related Functions

 COUNTIFS with multiple criteria and OR logic
=SUM(COUNTIFS(criteria_range,criteria))

To count based on multiple criteria using OR logic, you can use the COUNTIFS function with an array constant.

In the example shown, the formula in H6 is:

=SUM(COUNTIFS(D4:D11,{"complete","cancelled"}))

How this formula works

By default, the COUNTIFS function applies AND logic. When you supply multiple conditions, all conditions must match in order to generate a count.

One solution is to supply multiple criteria in an array constant like this:

{"complete","cancelled"}

This will cause COUNTIFS to return two results: a count for "complete" and a count for "cancelled", packaged in an array result like this:

{5,3}

To get a final total, we wrap COUNTIFS inside SUM. The SUM function then sums all items in the array and returns the result.

Be careful not to double-count

Whenever you use multiple criteria with OR logic, be careful not to double-count. In this example, the two criteria are mutually exclusive, but in other cases it is easy to accidentally double count by applying criteria that are too loose.
  

0 votes. 0 / 5