Maximizing Excel’s COUNTIF/COUNTIFS Functions with OR Logic
Maximizing Excel’s COUNTIF/COUNTIFS Functions with OR Logic In a previous post, we explored how to use SUMIF OR to add up multiple values in Excel. Now, let’s take things a step further and learn how to apply the same OR logic to the COUNTIF and COUNTIFS functions.
Have you ever needed to count more than one value from a single column or range of cells in Excel? If so, you’ve likely encountered a roadblock when using COUNTIF or COUNTIFS, which typically require a single condition or multiple conditions using AND logic.
Fortunately, there’s a simple yet powerful workaround that enables you to use OR logic with these functions. In this post, we’ll walk through how to apply the COUNTIF/COUNTIFS OR formula to count specific values from a given range of data.
To follow along, download the sample data file here and let’s dive in.
How to Use COUNTIF/COUNTIFS OR
Consider the following dataset of 50 people and their preferred social media platforms:
Your task is to count the number of people who like both “Facebook” and “Twitter”. Using the COUNIF function, enter the following formula into a cell and press enter:
=SUM(COUNTIF(B2:B51,{“Facebook”,”Twitter”}))
Alternatively, if you prefer to use COUNIFS, use the following formula:
=SUM(COUNTIFS(B2:B51,{“Twitter”,”Facebook”}))
By applying the COUNTIF/COUNTIFS OR formula, you can easily count multiple specific values within a given range, boosting your productivity and efficiency in Excel.
By using either of the formulas described above, you can obtain a result of 25, which is the combined count of Facebook (17) and Twitter (8).
To grasp the application of OR logic in COUNTIF and COUNTIFS functions, the formula should be divided into three parts. The COUNTIFS function utilizes AND logic, whereas COUNTIF allows only one value to be specified for counting. 
Initially, two different criteria were used in the formula via the array concept. Next, the COUNTIFS function was used to find these two values. Finally, the entire formula was enclosed in the SUM function to obtain the joint count of both values. This concept was explained in COUNTIF, but the COUNTIFS formula operates in the same way.
Shay asked how to use dynamic values instead of hardcore values in this formula while discussing the use of OR in SUMIF/SUMIFS. There are two minor modifications you can make to your formula to accomplish this. Instead of using curly brackets, use a named range (it’s best to use a table) for your values. Then, use Ctrl + Shift + Enter to input this formula as a proper array formula. As a result, your formula will be:
{=SUM(COUNTIFS(B2:B51,Named_Range))}
Using OR logic in COUNIF/COUNIFS can enhance its effectiveness and reduce the need to create lengthy formulas. Additionally, using dynamic criteria is incredibly beneficial. You can modify values whenever you want, without having to modify the formula.
I hope you found these formula tips helpful and that they will assist you in improving your efficiency. Please share your thoughts with me in the comment section, and don’t forget to share this tip with your friends.
