How to Use COUNTIF with OR in Excel

This guide will explain how to use the COUNTIF function with OR logic in Microsoft Excel.

We can use the COUNTIF function if we need to know how many values in a range fit at least one condition out of the several provided.

The COUNTIF function primarily counts the number of cells in a range that fit certain criteria. However, we can also use the COUNTIF function to count the number of cells that fall under one of several criteria.

Let’s take a look at a quick example where you might need to count cells that fit one of several criteria.

Suppose you have a table of customer data. You want to know the number of users from New York or Los Angeles. 

We can use the COUNTIF function to calculate the number of people from both New York and Los Angeles. We can add the result of both of these formulas to find the total.

You can also use an array of conditions as an argument in our COUNTIF function. We’ll need to use the SUM function to get the total of all the results.

Now that we know when to use the COUNTIF function, let’s learn how to use it and work on an actual sample spreadsheet.

A Real Example of Using COUNTIF with OR in Excel

The following section provides two different examples of how to use COUNTIF for several criteria. We will also explain the formulas and tools used in these examples.

First, let’s take a look at our sample data. We have a list of orders with a specified order amount and location. We want to know how many of our orders come from either New York or Los Angeles.

You can add two COUNTIF function results together to determine the count.

To get the value in D4, we just need to use the following formula:

=COUNTIF(A2:A21;"New York") + COUNTIF(A2:A21;"Los Angeles")

We can also apply an array argument to the COUNTIF function to achieve the same result.

To get the value in D4, we just need to use the following formula:

=SUM(COUNTIF(A2:A21;{"Los Angeles","New York"}))

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

Use our sample spreadsheet to see the difference between both methods yourself.

If you’re ready to try using COUNTIF with OR logic, head over to the next section to read our step-by-step breakdown on how to do it!

How to Use COUNTIF with OR in Excel 

This section will guide you through each step needed to start using COUNTIF with OR logic in Excel. You’ll learn how to add the results of two COUNTIF functions and how to use an array of conditions as an argument.

Follow these steps to start using the COUNTIF function for counting cells that fit one of several criteria:

1. First, let’s try to add two COUNTIF results together. Start by using the COUNTIF function to count cells that fit the first condition.
   In the example above, we used the COUNTIF function to count the number of orders coming from New York.
2. Next, use the ‘+’ addition symbol to add another COUNTIF formula. The second COUNTIF formula should query the same range. The addition symbol will ensure that the formula will follow OR logic since both results will count to the total.
   
3. Hit the Enter key to evaluate the formula.
   In the example above, we could determine that 14 of our orders came from either New York or Los Angeles.
4. Next, we’ll try the array method for using COUNTIF on multiple criteria. Start by adding a SUM function on a new cell.
   
5. Our SUM function’s argument is a single COUNTIF function. Indicate the range of the COUNTIF function.
   In our example, we want to count cells in the range A2:A21.
6. Next, we’ll provide an array of conditions for the second argument of the COUNTIF function. The function will return a range of values rather than a single value. This is why we needed to add a SUM function to get the total of all the COUNTIF results.
   
7. Hit the Enter key to return the final count.
   
   

These are all the steps needed to use COUNTIF with OR logic in Excel. 

This step-by-step guide should provide you with all the information you need to use the COUNTIF function with OR logic in Excel.

We’ve shown you how you can add multiple COUNTIF results together to determine the number of cells in a range fit one of several provided criteria. We’ve also explained how you can use an array of conditions as an argument for the COUNTIF function.

The COUNTIF function is just one example of the many Excel functions you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe