COUNTIF Function In Excel – Counting Cells With Condition

In this tutorial, we would learn how to count the number of cells based on some condition or criteria using the COUNTIF function in excel. This function belongs to the statistical function group and is almost similar to the previously learned excel’s COUNT and COUNTA formula.

This tutorial would cover the following areas of the COUNTIF formula: when to use, syntax, arguments, and examples of excel COUNTIF function in excel.

Here we go 🙂

When To Use Excel COUNTIF Formula

The word COUNTIF denotes “to count if “. We use this function when we need to count the number of cells only if the specified condition fulfills.

The COUNTIF function comes into its usage when you want to find the total number of students who scored more than 90 marks.

It returns the number of cells that corresponds to a particular string or numerical value.

Syntax and Arguments

=COUNTIF(range,criteria)

The following points will explain to you the function arguments.

  • range – In this argument, specify the range of cells to look for the number of cells.
  • criteria – In this argument, specify the condition/criteria to apply to the range argument.

There are no optional arguments in Excel COUNTIF Formula.

Points To Remember About COUNTIF Function of Excel

Before starting the actual usage of the COUNTIF function, you need to keep the following points in mind.

  • The function is not a case sensitive function.
  • We can apply only a single condition on the range of cells.
  • The function does not ignore text strings, logical values and blank cells.
  • The criteria argument accepts a number, text string, cell refrence with logical operator (<, >, <> ,=) or Wild Cards (*, ?).
  • Function returns a zero when no cell matches the condition.
  • While writing the COUNTIF function criteria, you must keep the following points in your mind:-
    • The text strings must be put within double quotes (“”) (see example 1).
    • Do not put the numbers and cell references within double quotes.
    • When using the logical operators in COUNTIF function
      • The expression of a logical operator with a constant number must be enclosed in double quotes. For instance, “>90”. (see example 2)
      • For expression of logical operator with cell reference, the operator (<, >, <> ,=) must be enclosed in double quotes while the cell reference or other formula is concatened with the expression using an ampersand (&). For instance, “>”&A1 or “>”&TODAY(). (see example 3)
    • Use wild card characters when you have a fussy guess over what you are lookig for in the range. There are the following wild cards in Excel
      • ? – for one missing character.
      • * – for a sequence of missing characters

Examples of COUNTIF Formula in Excel

In this section of the blog, we will be learning some of the examples for the Excel COUNTIF formula to understand how the function works.

Ex. 1 – Simple example of COUNTIF formula with Text String

Let us suppose we have the blood group of the students in a class in column A. There are four blood groups A, B, AB, O.

To find the number of students who have a B blood group, use the following formula.

=COUNTIF(A2:A12,"B")
COUNTIF function of Excel passing string Result

As a result, the function returns 5.

Explanation – The explanation of the above formula if very simple. The formula simply applies the criteria “B” on the range A2:A12, and counts the cells which fulfill this criteria. There is a total of 5 cells that match this COUNTIF function criterion (highlighted, hence formula outputs 5.

Ex. 2 – COUNTIF Value is More Than A Particular Value

The below image from excel shows the marks obtained by students in English subject.

COUNTIF function of Excel Raw data

Suppose you want to know how many students scored more than 80 marks. To achieve this, use the following formula:

=COUNTIF(A2:A13,">80")

As a result, you would get the formula output as 3.

Explanation – In this example, the range A2:A13 denotes the range of cells on which you want to check for the condition and count. The criteria “>80” enables excel formula to count the number of cells containing the value of more than 80. There are three cells (A1, A2, A9) that satisfy the condition. Therefore, the function returns 3.

Infographic - COUNTIF Formula Function in Excel

Ex.3 – Using Logical Operators with COUNTIF Formula in Excel

The logical operators are mathematical operators like >, <, <>, =.

Logical Mathematical Operators Excel

We can use the logical operators with constants, text strings, cell references, and functions. Let us take one sample data to understand it.

Let us suppose, the three sales team made following sales during different months of the year.

COUNTIF function of Excel Using logical operator raw data

Suppose, we would like to find the total number of months when the sales made is greater than 20000. To achieve this, use the following formula:

=COUNTIF(C2:C13,">20000")

Result: 5

Similarly, to find the total number of months when the sales value is less than C3, use following formula:

=COUNTIF(C2:C13,"<"&C3)

Result: 7

Likewise, the below formula will return the number of months when the sales were not made by team C (i.e. by only A and B).

=COUNTIF(B2:B13,"<>C")

Result: 10

COUNTIF function of Excel Using logical operator result

Explanation – In the first formula we apply criteria “>20000” on the range A2:A13. The cells satisfying this condition are C3, C6, C9, C10, C13. Thus the result of the formula is 5.

In the second formula The Criterion is “<“&C3. The cell C3 contains 22000 and the cells having value less than 22000 are C2, C4, C5, C7, C8, C11,C12. Thus the total number of cells are 7.

For the third formula, the criteria “<>C” is applied on the range B2:B13. The cells containing C Team in column B (B7, B10) are excluded and the remaining total number of teams ( A and B) are 10.

Ex. 4 – Using Wild Card Characters With Excel COUNTIF Function

Wild card characters in Excel are used for a partial match of data.

The below image shows data about the menu of a bakery. Now, we want to know the types of pastries that are available.

COUNTIF function of Excel with wild card characters raw data

Enter the following COUNTIF formula to know the variety of pastries in the bakery.

=COUNTIF(A2:A13,"*Pastry")
COUNTIF function of Excel with wild card characters result

As a result, the function has returned 6 denoting that there are six types of pastries in the Bakery.

Explanation – In the above example, the range is A2:A13 and the criteria is “*Pastry”. Here, asterisk (*) is a wild card character that signifies a sequence of unknown characters before “Pastry”. The cells containing text strings ending with the text ‘Pastry’ are highlighted.

With this, we have completed this blog on how to use the COUNTIF function in Excel.

Thank you for reading 🙂

Leave a Comment