Pinterest Pixel

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... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

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

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.

See also  Excel Made Easy: How to Use COUNTIF with Multiple Criteria

=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)

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

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

See also  How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

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))}

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

  • 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.
See also  Top 5 Ways to Sum a Row in Excel

 

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))}

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

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

See also  10 + 1 Ways to Round Numbers in Excel

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
3 Simple Methods to Explore the Power of COUNTIF with OR Logic in Excel | MyExcelOnline 3 Simple Methods to Explore the Power of COUNTIF with OR Logic in Excel | MyExcelOnline
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.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...