In this chapter of Sum and Count formulas, we will learn OR criteria to count or sum values with two different criterias. We have made video above to understand AND & OR criteria, please do follow it or you can follow the guide below.

Download Practice Sheet

Previously :- AND Criteria In Excel In 2 Simple Steps

What is OR Criteria?

OR Criteria in excel simply means to include all the criteria defined in the function, Let’s say, To count the number of Johns in class 1 and Linda in class 2 differently and sum of all them not the minimum (as we have done in AND criteria), then with the help of multiple COUNTIF Function we can perform the task using the “+” operator in the formula itself. It may sounds confusing, but when we do the practice of the same, you will get to know the same.

Below is the sample data we use for calculating the total number of John and Linda like we did in AND Criteria;

OR Criteria In Excel in 2 Simple Steps 10

STEP1 – COUNTIF Function for First Criteria

We will use COUNTIF Function twice in order to find the total count of john and linda respectively. Therefore in A9 cell, we will input COUNTIF Function for Comp 1;

OR Criteria In Excel in 2 Simple Steps 11

Next, In range argument of COUNTIF Function, we will select the range to calculate the number of john count in comp 1 i.e. A2:A7;

OR Criteria In Excel in 2 Simple Steps 12

In next criteria argument of COUNTIF Function, we will quote the name of john i.e. “John” like below;

OR Criteria In Excel in 2 Simple Steps 13

Now we will close the parenthesis and add “+” operator for the linda in comp 2;

OR Criteria In Excel in 2 Simple Steps 14

STEP2: COUNTIF Function for Second Criteria

Now we will add the same COUNTIF Function to count number of Linda in Comp 2;

OR Criteria In Excel in 2 Simple Steps 15

Similar to John operation, We will now select the range B2:B7 in the first argument of COUNTIF i.e. range;

OR Criteria In Excel in 2 Simple Steps 16

In next criteria argument of COUNTIF, we will quote the name of linda i.e. “Linda” and close the parenthesis;

OR Criteria In Excel in 2 Simple Steps 17

Now we click the ENTER and we got the answer i.e. 5 is the total count of John and Linda in both competitions i.e. Comp 1 and Comp 2.

OR Criteria In Excel in 2 Simple Steps 18

Therefore, OR Criteria does count or sum all the values of multiple criteria you have entered in the formula on the contrary to the AND Criteria. This is very useful formula when you want to add multiple sales values with multiple criteria i.e. on the basis of office, transaction date, client etc.

We hope you got to know the difference between AND Criteria and OR criteria in excel.

Learn Next:-