In this chapter of Count and Sum formulas, we will cover the AND Criteria to count or sum your values in excel. Do watch the video above to better understand AND criteria and OR criteria, along with the practice sheet attached below.
Previously In Count and Sum – Count Errors In Excel in 2 Simple Steps
What is AND Criteria?
In Excel AND Criteria is basically used in logical operations where you need to satisfy two or more condition in a single formula. For Example, You want to know how many Johns are there in class 1 and class 2 combined , than you can use COUNTIFS function to count the same with two logical tests.
Note: And criteria doesn’t add up all values, let’s take the above example, If there are 4 johns in class 1 and 5 Johns in Class 2, then with AND Criteria it will give you 4 Johns because the count should be the same with and criteria. If you want to find out the total count of johns in both classes then you should learn about OR Criteria.
Below is our sample data which you can download using the link above, We have data of 2 competitions namely, Comp1 and Comp2 below;
STEP1 – Using COUNTIFS Function For Criteria 1
What we are going to do is, we will find the minimum number of John in comp1 and Linda in comp2 not the total number, Therefore we will use the COUNTIFS function to evaluate it.
Firstly, We will call COUNTIFS function in A9 cell as below;
As you know COUNITFS is multiple criteria based function which has two argument for single criteria.
First is Criteria__range1, In this we will select the range A2:A7 to calculate minimum number of john in comp1 to linda in comp2.
Second is criteria1, Here we will add the criteria for criteria_range1 i.e. “John” to count number of john in comp1;
STEP2 – Using COUNTIF Function for Criteria 2
The first criteria is completed, now moving onto the next criteria i.e. Criteria_range2, to count Linda in comp2, therefore we will select the range B2:B7;
Next is criteria2, We will add “Linda” as our criteria2 to count the number of linda in comp2 like we have done in criteria1 for john;
Now, we will close the parenthesis of COUNTIFS function and click ENTER, here we find out that there are atleast 2 number of John in comp1 and Linda in comp2 using the AND Criteria. If you want to find out the total number of them, then you can use the OR criteria explained in the video.
AND Criteria in excel lets you know the common factor of two conditions stated in your arguments or criteria. Here we had 2 Johns in comp1 and 3 Linda in comp2, So wanted to find out the minimum repetition of both competitors, Therefore, We used the AND Criteria to evaluate the same.
Learn Next :- OR Criteria In Excel