Mursalin Ibne Salehin
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.
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.
