3 Simple Methods to Explore the Power of COUNTIF with OR Logic in Excel

Excel is a very versatile and powerful tool used for data analysis, calculation, and manipulation. One of the most used functions is COUNITF in Excel which enables cell counting based on specific conditions. The integration of the COUNTIF with OR operator enhances the function’s flexibility, making it even more powerful.

COUNTIFS can be used when we are dealing with multiple conditions but it uses the AND logic i.e. it will count the cells only when all the conditions are met. What if we want to count the cells even if at least one criterion is met? This is where COUNTIF with OR comes to our rescue.

In this article, we will be exploring the different ways of applying the COUNTIF with OR logic –

Let’s look at each of these one by one.

Download the Excel Workbook below to follow along and understand How to Use the COUNTIF with OR logic in Excel

download excel workbookCOUNTIF-with-OR.xlsx

Count cells using a + sign

The easiest way is to count the values individually and then use a plus (+) sign to add them together.

=COUNTIF(range, critera1) + COUNTIF(range, criteria2)

Let’s use an example to understand better.

In this example, we have a table containing the results of a marathon race. Column A contains the names of the competitors, column B is their respective districts, and column C is whether they completed the race or did not complete the race.

We want to find out how many competitors are from Wellington and Waterloo. We will be using COUNITF to individually count the cells for each criterion and then add them together. The formula will be:

=COUNTIF(B2:B25,F1)+COUNTIF(B2:B25,F2)

• B2:B25 specifies the range of cells in which we want to search for matches.
• COUNTIF(B2:B25,F1) counts the number of cells within the range B2:B25 that match the value in cell F1 i.e. Wellington.
• Similarly, COUNTIF(B2:B25,F2) counts the number of cells within the range B2:B25 that match the value in cell F2 i.e. Waterloo.
• The two COUNTIF results are then added together using the plus (+) operator.

Here, Excel will count the number of cells in the range B2:B25 that contain either “Wellington” or “Waterloo”, and provide the combined count of those cells. Hence, it will use the COUNTIF with OR logic and provide the results.

Count cells using a SUM function

If we have only a few criteria, you can use the plus (+) sign to sum up the COUNTIF function. However, as the number of criteria increases, this approach can become tedious and cumbersome.

In such situations, a better approach is to use the SUM function along with COUNTIF and an array formula. This method simplifies the process and makes it easier to handle multiple criteria efficiently. The syntax will be –

{=SUM(COUNTIF(range, criteriarange))}

Let’s use an example to understand better. In this example, we want to find out how many competitors are from Wellington, Waterloo and Westwood. The formula for this using COUNTIF with OR logic will be –

{=SUM(COUNTIF(B2:B25,F1:F3))}

• B2:B25 specifies the range of cells in which we want to search for matches.
• F1:F3 specifies the range of cells containing the criteria i.e. Wellington, Waterloo, and Westwood.
• COUNTIF function will count the cells that match the 3 criteria individually
• SUM function will sum all items in the array and returns the result.
• Press CTRL + SHIFT + Enter to use a array function.
• The use of curly brackets “{ }” around the formula indicates that it is an array formula, which allows it to perform calculations across multiple cells simultaneously.

Count cells using a SUMPRODUCT function

We can even use SUMPRODUCT to count the cells using COUNTIF with OR logic. The formula will be –

{=SUMPRODUCT(1*(range =criteriarange …))}

Let’s use an example to understand better. In this example, we want to find out how many competitors are from Wellington, Waterloo, and Westwood. The formula for this using COUNTIF with OR logic will be –

{=SUMPRODUCT(1*COUNTIF(B2:B25,F1:F3))}

• COUNTIF(B2:B25,F1:F3) – It will compare each criterion i.e. Wellington, Waterloo, and Westwood with the range B2:B25 and provide TRUE when the criteria match and FALSE otherwise.
• 1*(range =criteriarange …) – Multiplication sign will convert the TRUEs to 1s and FALSEs to 0s.
• SUMPRODUCT function will now add up all the numbers in the array.
• Press CTRL + SHIFT + Enter to use an array function.

Conclusion

This article explores different methods of applying the COUNTIF with OR logic, including using the plus sign, SUM function, and SUMPRODUCT function, to efficiently count cells based on multiple criteria in Excel. By utilizing these different techniques, you can effectively count cells based on multiple criteria using COUNTIF with OR logic.

Each method offers its advantages depending on the number of criteria and complexity of the analysis.

If you like this Excel tip, please share it

email

Pinterest

Facebook

Twitter

Linkedin

John Michaloudis

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.