**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))}`

## Leave a Comment