The Excel COUNTIF() function is an incredibly flexible function, letting you count values if they meet a condition. One thing that’s not clear, is how to count cells that meet multiple different conditions when any of them should be included. In this tutorial, you’ll learn how to use the Excel COUNTIF() function using OR logic.
By the end of this tutorial, you’ll have learned the following:
- How to use the COUNTIF() function with OR logic
- How to use the COUNTIF() function with dynamic arrays
Understanding the COUNTIF() and COUNTIFS() Functions in Excel
The Excel COUNTIF() function and COUNTIFS() function are used to count cells that meet different criteria.
- The Excel COUNTIF() function counts cells that meet a single criteria
- The Excel COUNTIFS() function counts cells that meet one or more criteria
Because the functions are so similar, let’s take a look at how the COUNTIF() function is written.
=COUNTIF(range, criteria)As you can see from the code block above, the function takes two arguments. Let’s take a closer look at them in the table below:
| Argument | Description | Required? |
|---|---|---|
range= | The range of cells to apply the condition to | Yes |
criteria= | The condition to apply, including any of the logical operators that may be required | Yes |
The Excel COUNTIFS() function works in the same way, though you can append multiple ranges and criteria following the first set. One important thing to note is that the COUNTIFS() function uses AND logic, ensuring that all of the conditions are met. Because of this, we’ll need to use a different approach to count cells using OR logic.
How to Use COUNTIF() With OR Logic
To use the Excel COUNTIF() function with OR logic, we can simply use the function multiple times and add the values together. This allows us to combine multiple conditions, even if they’re mutually exclusive.
This is very different than passing in both conditions into the COUNTIFS() function. Doing this attempts to count cells where both conditions are included, using AND logic.
Let’s take a look at an example of how we can use the COUNTIF() function multiple times to use OR logic:
In the example above, we used the Excel COUNTIF() function to count cells where the region was either North or South. In order to do this, we use the COUNTIF() function twice – once for cells that are equal to North and once for cells that are equal to South. We then add these two values together. In this case, we count 3 cells that are equal to South and 3 that are equal to North, for a total of 6.
You can chain this as many times as you like, in order to count multiple conditions using OR logic. In order to simplify this, we can also use dynamic array functions. This is what you’ll learn in the following section.
How to Use COUNTIF() With OR Logic Using Dynamic Arrays
Dynamic array functions allow for you to simplify your code and make it more dynamic at the same time. We can replicate the COUNTIF() function with OR logic example above, by passing an array of values into the COUNTIF() function. We create these arrays by passing the values into curly braces. If we wanted to count cells where the region was equal to North or South, we could write {"North", "South"}.
Let’s see how we can use array functions to calculate COUNTIF() with OR logic. Use the formula below:
=COUNTIF(B3:B13, {"North", "South"})Let’s see what happens when we use this function in Excel:
We can see that by using the function, Excel returns two cells of data. In the screenshot above, you can see that the values 3 and 3 are returned. The values are wrapped in a blue border, meaning that they are part of a spill-over array. While this does tell us how many cells meet the different conditions, it doesn’t provide a single value.
Let’s see how we can wrap the COUNTIF() function in the SUM() function to provide a count of all cells that meet any of the conditions.
In the example above, we wrapped the function in the SUM() function, using the formula below:
=SUM(COUNTIF(B3:B13, {"North","South"}))By wrapping the COUNTIF() function in the SUM() function, the function returns only a single value. This value represents the count of all the cells meeting the given conditions, using OR logic.
Conclusion
In this tutorial, you learned how to use the Excel COUNTIF() function with OR logic. This means that you can count cells that meet any of the conditions that you pass in. By default, the COUNTIFS() function will use AND logic, meaning that the function counts only cells that meet all of the conditions. You first learned how to count cells meeting any condition by adding multiple COUNTIF() functions together. Then, you learned how to use dynamic arrays to use OR logic with the COUNTIF() function, by wrapping the result in the SUM() function.
Additional Resources
To learn more about related topics, check out the tutorials below: