How to Use COUNTIFS with Multiple Criteria and OR Logic

When you need to manage data in Excel with multiple criteria, one of your options is to use the COUNTIFS function. If there are several choices in your array, you might also combine this with the OR logic. 

Generic formula for COUNTIFS

=SUM(COUNTIFS(red,{"","black","green","blue"}))

Application example of COUNTIFS with OR logic

COUNTIFS with multiple criteria and OR logic is a powerful technique for data management on Excel sheet. The COUNTIFS function makes things quite easy. Check out this example on how to manage a competition:

Figure 1. Example 1 of COUNTIFS Function

The formula in H7 is:

=SUM(COUNTIFS(D5:D14,{"complete","pending"}))

How COUNTIFS with multiple criteria OR logic works

COUNTIFS function will be default apply AND logic in the formula. Therefore, when multiple conditions are supplied, they must all match to generate a count. In this cases, this is the array supplied as multiple criteria/condition:

{“complete”,”pending”}

…with that, COUNTIFS function will return two count outcomes: “complete” and “Pending” counts, and the array result looks like this:

{4,2}

The wrapping of COUNTIFS inside SUM is what gives the final total. The SUM function sums all the items in the array to give a final total which is H7 or 6.

How to add criteria with COUNTIFS

For additional criteria to the formula, you’ll need a single column array for every criterion as well as arrow array for the other. What if you to count games that “complete” or “pending” for either Lakers or Mckenz. Here is the formula:

=SUM(COUNTIFS(D5:D14,{“complete”,”pending”}, C5:C14,{“Lakers”;”McKenz”}))

The semi-colon in the second array constant is responsible for the creation of a vertical array. That’s crucial because excel can only pair elements in the two array constant. In this case, it will return a two-dimensional array of results that looks like this:

=SUM({1,0,1,0})

The answer is 2. It’s how COUNTIFS with multiple criteria and OR logic can make counting simple.

Cell reference for COUNTIFS criteria

This is quite simple. To use a cell reference for criteria, this the perfect array formula:

={SUM(COUNTIFS(range1,range2))}

Still need some help with Excel COUNTIFS or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar