Countif Excel –  How to use this function to solve your counting problems

Countif Excel – How to use this function to solve your counting problems

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

countif excel example

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

countif with one condition

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

countif function with two condition

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

count ending with 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”)

count ending with es and exactly 7 letters

Related topics:

transpose rows
Excel Data entry form

Express your thoughts below. Share this article if you think it is useful

Share:

Leave a Reply