Excel COUNTIFS with Multiple Criteria and OR Logic (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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 satisfy 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.

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.

countifs with multiple criteria and or logic


1. Using 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.

Use Excel SUM and COUNTIFS Functions with Multiple Criteria and OR Logic

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.

Use Excel SUM and COUNTIFS Functions with Multiple Criteria and OR Logic

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. Applying 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.

Excel COUNTIFS Function with Plus Operator to Count Cells with Multiple Criteria

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. Counting 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.

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

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 and MATCH functions. 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.

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

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.

How to Apply COUNTIFS Function with Dynamic OR Logic in Excel

  • 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.

How to Apply COUNTIFS Function with Dynamic OR Logic in Excel

  • After that, select Cell F13 and type the formula below:
=SUM(COUNTIFS(B5:B13,Product))
  • Press Enter to see the result.

How to Apply COUNTIFS Function with Dynamic OR Logic in Excel

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

How to Apply COUNTIFS Function with Dynamic OR Logic in Excel

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.

How to Use COUNTIFS Function with Wildcard Characters in Excel

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


Download Practice Workbook

You can download the practice book from here.


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. If you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


<< Go Back to Excel COUNTIFS Multiple Criteria | Excel COUNTIFS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo