Last Updated on November 11, 2019 by Skillslelo
About COUNTIF:
Countif is one of the most useful functions of Excel that counts the number of cells within a specified range that meet the given criteria. If you have your criteria for what should get counted and what should not, you can use the countif excel function to apply that condition. Here is the syntax of countif excel function:
= COUNTIF(range,criteria)
Range : Your range can be one or more cells on which you want to apply your criteria or condition. For example, A1:A5, A1:B6 etc.
Criteria : Your criteria is the condition that you want to apply on the range. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.
1⃣ EXAMPLE
Suppose you want to know how many cells contain a value greater than 500 in the given range (A2:A9). Use the following formula to know the number of cells:
= COUNTIF(A2:A9, “>500”)
Here A2:A9 is your range and “>500” is your criteria. As you can see in the formula cell in the figure. This returns the value 6. Hence there are 6 numbers that are greater then 500.
2⃣ EXAMPLE
To count the number of cells with banana in the first column range (A1:A11). Use one of the following formulas to count it:
=COUNTIF(A1:A11,”banana”)
=COUNTIF(A1:A11,”B5″)
3⃣ EXAMPLE
To count the total number of cells with banana and apples in the first column range (A1:A11). Here is the formula to know it:
=COUNTIF(A1:A11,”banana”)+ COUNTIF(A1:A11,”apples”)
Here A1:A11 is your range and “banana” and “apples” is your criteria. As you can see in the formula bar. This returns the value 4. So the total number of banana and apples are 4.
4⃣EXAMPLE
To count the number of cells ending with the letters “es” in the first column range (A1:A10). Use the following formula to count it:
=COUNTIF(A1:A10,”*es”)
5⃣ EXAMPLE
To find number of cells ending with the letters “es” and having exactly 7 letters in the first column range (A1:A10). Use the following formula to count it:
=COUNTIF(A1:A10,”?????es”)
Related topics:
Express your thoughts below. Share this article if you think it is useful