< Go Back

COUNTIFS with multiple criteria and OR logic

Generic Formula

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

Application Example

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

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 Criteria

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

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

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
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar