In this article, we will learn to use the **COUNTIFS function with multiple criteria and OR logic in Excel**. The **COUNTIFS function** counts the number of cells that satisfies a set of given conditions or criteria. Today, we will demonstrate **3 **ideal examples. Using these examples, you can use the** COUNTIFS function with multiple criteria** and **OR **logic. Also, we will show an alternative way to count with multiple criteria. So, without further delay, let’s start the discussion.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice book from here.

## 3 Ideal Examples of Excel COUNTIFS with Multiple Criteria and OR Logic

To explain the examples, we will use a dataset that contains information about the **Payment Status **and **Quantity **of some **Products**. We will use the **COUNTIFS **function to count the number of cells based on multiple criteria and OR logic. For example, we want to count the number of laptops and keyboards with pending or paid status. In that case, we can use the following examples. Also, you can apply them to solve your problems easily.

### 1. Use Excel SUM and COUNTIFS Functions with Multiple Criteria and OR Logic

In the first example, we will use the **SUM **and **COUNTIFS **functions with multiple criteria and **OR **logic. Here, we will count the number of cells that contain a **Laptop **or **Keyboard** with the **Paid **or **Pending **payment status. In the dataset below, you can see **4 **sets of cells that satisfy the conditions.

Let’s follow the steps below to see how we can create a formula to count the number of cells with multiple criteria.

**STEPS:**

- First of all, select
**Cell F13**and type the formula below:

`=SUM(COUNTIFS(B5:B13,{"Laptop","Keyboard"},D5:D13,{"Paid";"Pending"}))`

- Secondly, press
**Enter**to see the result.

Here, we have used the **SUM **and **COUNTIFS **functions together to get the result. Inside the **COUNTIFS **function, we have entered the criteria range and criteria.

The first criteria look for a **Laptop **or **Keyboard **in the **range B5:B13 **and the second criteria looks for **Paid **or **Pending **payment status in the **range D5:D13**.

**🔎 How Does the Formula Work?**

**COUNTIFS(B5:B13,{“Laptop”,”Keyboard”},D5:D13,{“Paid”;”Pending”}):**The output of this part is an array. And that array is**{2,0,1,1}**. The array can be displayed like the picture below:

It shows that the number of **Laptops **with the **Paid **status is **2** and the **Pending **status is **1**. Similarly, the number of **Keyboards **with **Paid **status is **0 **and the **Pending **status is **1**. So, the total number is **4**.

**SUM(COUNTIFS(B5:B13,{“Laptop”,”Keyboard”},D5:D13,{“Paid”;”Pending”})):**The**SUM**function adds up the array**{2,0,1,1}**and displays the result which is**4**.

**Read More:** **Excel COUNTIFS Not Working with Multiple Criteria (2 Solutions)**

### 2. Excel COUNTIFS Function with Plus Operator to Count Cells with Multiple Criteria

We can also use the **COUNTIFS **function with the **Plus **(**+**) operator to count cells with multiple criteria and **OR **logic. Here, our goal is the same as in the previous example. We will try to count the number of cells that contain the **Laptop **and **Keyboard **with **Paid **or **Pending **status. For that purpose, we will use a slightly different dataset from the previous one. Here, we have typed the **Status **in **Cell F9 **and **Cell F10**.

Let’s follow the steps below to see how we can implement the formula with the **Plus **(**+**) operator.

**STEPS:**

- Firstly, select
**Cell F13**and type the formula below:

`=COUNTIFS(B5:B13,$F$6,D5:D13,$F$9)+COUNTIFS(B5:B13,$F$6,D5:D13,$F$10)+COUNTIFS(B5:B13,$F$7,D5:D13,$F$9)+COUNTIFS(B5:B13,$F$7,D5:D13,$F$10)`

- After that, press
**Enter**to see the result.

Here, we have used **absolute cell reference **instead of typing the text inside the formula.

**🔎 How Does the Formula Work?**

We can break the formula into four parts and each part counts the cell number specified by the condition.

**COUNTIFS(B5:B13,$F$6,D5:D13,$F$9):**This is the first part of the formula. It counts the number of cells that contain**Keyboard**in the**range B5:B13**and**Pending**in the**range D5:D13**.**COUNTIFS(B5:B13,$F$6,D5:D13,$F$10):**The second part counts the number of cells that contain**Keyboard**in the**range B5:B13**and**Paid**in the**range D5:D13**.**COUNTIFS(B5:B13,$F$7,D5:D13,$F$9):**Similarly, the third part counts the number of cells that contain**Laptop**in the**range B5:B13**and**Pending**in the**range D5:D13**.**COUNTIFS(B5:B13,$F$7,D5:D13,$F$10):**The last part counts the cell numbers that store**Laptop**in the**range B5:B13**and**Paid**in the**range D5:D13**.- The
**Plus**(**+**) operator sums the value and shows the summation.

**Read More:** **COUNTIFS Function in Excel with Multiple Criteria from Different Sheet**

### 3. Count Cells Using Excel COUNTIFS Formula with OR as well as AND Logic

In the third example, we will count cells using the **COUNTIFS **function with **OR **as well as **AND **logic. Here, we will count the number of cells that contain a **Laptop **or **Keyboard **in the **range B5:B13 **and payment status **Paid **in the range **D5:D13**. For that purpose, we will use the dataset below.

Let’s observe the steps below to see how we can count cell numbers with **OR **as well as **AND **logic.

**STEPS:**

- In the first place, select
**Cell F12**and type the formula below:

`=COUNTIFS(B5:B13,F6:F7,D5:D13,F10)`

- Now, press
**Ctrl**+**Shift**+**Enter**to see the result.

This formula is an **array formula**. That is why we got an array **{0,2}** in the result.

- The first condition looks for the text
**Keyboard**or**Laptop**in the**range B5:B13**. - Similarly, the second condition looks for the word
**Paid**in the**range D5:D13**.

- To get the summation of the array
**{0,2}**, you need to use the formula below:

`=SUM(COUNTIFS(B5:B13,F6:F7,D5:D13,F10))`

If you insert this formula in **Cell F12**, then it will show **2 **in that cell.

## Alternative to Excel COUNTIFS Function to Count Cells with Multiple Sets of OR Criteria

The alternative to the **COUNTIFS **function is to use the **SUMPRODUCT **function. Inside the **SUMPRODUCT **function, we will have to use **the ISNUMBER function **and **the MATCH function**. To understand the use of the **SUMPRODUCT **function, we will use the dataset of **Example 1**. Here, we want to find the count of cells that contain the word **Laptop **or **Keyboard **with the payment status **Paid **or **Pending**.

To do so, you need to follow the steps below:

**STEPS:**

- In the beginning, select
**Cell F13**and type the formula below:

`=SUMPRODUCT(ISNUMBER(MATCH(B5:B13,{"Laptop","Keyboard"},0))*ISNUMBER(MATCH(D5:D13,{"Paid","Pending"},0)))`

- After that, press
**Enter**to get the result.

Here, the **MATCH **function looks for the exact match of the texts in the desired range. After that, the **ISNUMBER **function checks if the returned value is a number or not. If it is a number, then it returns **TRUE**, otherwise **FALSE**. Let’s go through the formula breakdown to learn more.

**🔎 How Does the Formula Work?**

**MATCH(B5:B13,{“Laptop”,”Keyboard”},0):**This part returns the array:

`{#N/A, 2, #N/A, 1, 2, #N/A, 2, 1, 1}`

**ISNUMBER(MATCH(B5:B13,{“Laptop”,”Keyboard”},0)):**It checks if the returned array contains a number. The output of this part is:

`{FALSE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE}`

**ISNUMBER(MATCH(D5:D13,{“Paid”,”Pending”},0)):**Similarly, the output of this part is:

`{TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE}`

**ISNUMBER(MATCH(B5:B13,{“Laptop”,”Keyboard”},0))*ISNUMBER(MATCH(D5:D13,{“Paid”,”Pending”},0)):**This part multiplies the previous two arrays. So, the result becomes:

`{0, 0, 0, 1, 1, 0, 0, 1, 1}`

- Finally, the
**SUMPRODUCT**function returns the sum of the products of that array which is**4**.

## How to Apply COUNTIFS Function with Dynamic OR Logic in Excel

In the previous sections, we showed the **COUNTIFS **function with a formula that contained hardcore values. In that case, if you need change, then you have to make change changes inside the formula.

But we can also use dynamic **OR **logic inside a formula. For that purpose, we need to define names before applying the formula. Here, we will count the number of cells that contain the text **Laptop **or **Keyboard **in the **range B5:B13**. Let’s follow the steps below to see how to define names in Excel and use it inside the formula.

**STEPS:**

- Firstly, select the
**range F5:F6**.

- Secondly, go to the
**Formulas**tab and select**Define Name**.

- A box will appear.
- Type a name in the
**Name**field and click**OK**to proceed. - Here, we have named the
**range F5:F6**as the**Product**.

- After that, select
**Cell F13**and type the formula below:

`=SUM(COUNTIFS(B5:B13,Product))`

- Press
**Enter**to see the result.

- Finally, if you remove the word
**Laptop**from**Cell F6**, then the result of**Cell F13**will automatically update.

**Read More:** **How to Use COUNTIFS with Date Range and Text in Excel**

## How to Use COUNTIFS Function with Wildcard Characters in Excel

In **Excel**, we can use the **Question Mark **(**?**) and **Asterisk **(*****) wildcard characters in the **COUNTIFS **function. The **Question Mark **(**?**) matches a single character and the **Asterisk **(*****) symbol matches a sequence of characters. To demonstrate the wildcard characters, we will use the dataset below. From the dataset, we will count the number of cells with payment status.

Let’s follow the steps below to see how we can use the wildcard characters.

**STEPS:**

- First of all, select
**Cell F11**and type the formula below:

`=COUNTIFS(B5:B13,"*",D5:D13,"<>"&"")`

- After that, press
**Enter**to see the result.

This formula matches the sequence of the **range B5:B13 **& **D5:D13** and finds the non-empty cells.

## Conclusion

In this article, we have **3 **ideal examples of **Excel COUNTIFS Function with Multiple Criteria and OR Logic**. I hope this article will help you to perform your tasks efficiently. Moreover, we have also discussed the **Alternative Way to Count Cells with the** **SUMPRODUCT Function** **in Excel**. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit **the ExcelDemy website** for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.