How to Use the COUNTIFS and COUNTIF Function in Excel

The COUNTIFS and COUNTIF function in Excel are predefined functions that count cells based on specified criteria. You can use either function to count a range of cells when there are conditions associated with your data.

For example, you may want to know the total number of female students in a class. To count the number of students based on the condition (female students), you will employ the COUNTIF function. If there is more than one criterion, you employ the COUNTIFS function.

The COUNTIF function in excel is a legacy function that has been part of the MS Excel 2003 version. But the COUNTIFS function was introduced in MS Excel 2007 and has been useful in counting based on multiple criteria.

While the COUNTIF function accepts one criterion, the COUNTIFS function accepts multiple criteria.

In this tutorial, you will learn how to use the COUNTIFS and COUNTIF functions in Excel.

The COUNTIF Function in Excel

The COUNTIF function in Excel is used to calculate the total number of a range of cells, based on specified criteria. For example, the worksheet below displays the Math score of students based on sex and class. To count the total number of students in SS2A, I will use the COUNTIF function as follow: =COUNTIF(D2:D18, “SS2A”)

The above function states that Excel should count the text in the range D2 to D18 if the text value in the range is SS2A. To execute the function, Excel will count all SS2A that appeared in column D. When all items are selected and counted, the value will be placed in the appropriate cell.

You can also count how many students scored 50 and above in the Math column. To do this, I will execute the following function: =COUNTIF(E2:E18,“>=50”). In this example, my criteria are 50 and above. This leads us to the COUNTIF syntax.

The COUNTIF syntax

The syntax for the COUNTIF function in Excel is given by: COUNTIF(range, criteria)

Where:

• range: is an argument that must be specified (required). This argument represents the range of cells that should specify the COUNTIF criteria. See the example above. Such ranges can contain text, numbers, references, and dates.
• criteria: This is also a required argument that must be specified in the function. The criteria can be a number, text, cell reference, or function. It is used to define the cells that will be counted. When the criteria specified are met, the cells will be counted.  Criteria can also accept wild characters such as ? or *. The ? is used to match a single character while * is used to match a sequence of characters. A single character match can be like this, “f?r”. It will match farm, far, Farouk, etc. A sequence character match can be:
  • *oon: referring to cells that end with noon. E.g. afternoon, honeymoon, etc
  • Good*: referring to cells that begin with good. E.g. good morning, good day, etc,
  • *m*: referring to cells that contain ‘m’. e.g. Amaka, Amara, etc.

Example of COUNTIF function in Excel

The worksheet below displays the sales record of Wamco Int’l in three regions: East, West, and South. The general manager wants to know the number of sales made by each sales rep. in the Eastern region for the month under review. You can use the COUNTIF function to analyze the data whereby the Sales Reps will form the criteria for the count.

The solution is given as follow:

There are two sales reps in each region including the Eastern region being evaluated.

1. Create two rows to represent the total for each of the eastern sales reps as illustrated in the worksheet above.
2. Under Harry Johnson, enter the formula: =COUNTIF(D3:D58,”Harry Johnson”)
3. Copy the above formula and paste it under the cell representing Catson Veroni.
4. Change “Harry Johnson” to “Catson Veroni” and press ENTER on the keyboard.

From the solution, we observe that Harry Johnson made a total of ten (10) sales in the month under review. However, Catson Veroni made a total of twelve (12) sales. Remember that this solution has nothing to do with the amount of sales made by each sales rep.

The COUNTIFS function in Excel

The COUNTIFS function in Excel is used to count the values in a range of cells, based on more than one criteria. The COUNTIFS function can allow up to 127 range/criteria pairs when analyzing data in Microsoft Excel.

For example, the worksheet below displays the Maths score of students based on sex and class. To count the number of female students in SS2A, I will use the COUNTIFS function as follow: =COUNTIFS(C2:C18,”Female”,D2:D18,”SS2A”)

The above function states that MS Excel should count the values in the range C2 to D18. This count will be evaluated only when the value of C is female and the value of D SS2A. This implies that a female value must have a corresponding SS2A value to be counted, else the count will not execute.

To execute the function, Excel will trace where Female appears in column C, and where SS2A appears in column D and count the corresponding value. When all items are selected, the result will be placed in the selected cell.

The COUNTIFS syntax

The syntax for the COUNTIFS function is given by: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2, … criteria_rangen, criterian])

Where:

• Criteria_range1: This is a required argument that must be specified in the function. The criteria_range1 and criteria1 set up a search pair so that the item in criteria1 will be searched for in criteria_range1. If a match is found, the count takes place, else, the count is omitted.
• criteria1: criteria1 is a required argument. It defines which cells in criteria_range1 will be counted in the function. The criteria can be a number, text, cell reference, or function. When the criteria specified are met, the corresponding cells will be counted.  Criteria can accept wild characters such as ? or *. The ? is used to match a single character while * is used to match a sequence of characters. A single character match can be like this, “f?r”. It will match farm, far, Farouk, etc. A sequence character match can be:
  • *oon: referring to cells that end with noon. E.g. afternoon, honeymoon, etc
  • Good*: referring to cells that begin with good. E.g. good morning, good day, etc,
  • *m*: referring to cells that contain ‘m’. e.g. Amaka, Amara, etc.
• criteria_range2, criteria2 … criteria_rangen, criterian: These are optional ranges and criteria up to 127 range/criteria pair. It is used to define the number of range/criteria pairs for the COUNTIFS function. Any of them can be omitted in the function.

Take note of the following when using the COUNTIFS function:

• Always ensure that text values are in quotation marks when specifying criteria. E.g. “Male”.
• Use wild characters to help match multiple cells.
• You can introduce the comparison operators when using the COUNTIFS function. For example, =COUNTIFS(A2:A10, “>=25”,…)
• Each additional range in the argument must have the same number of columns and rows as the criteria_range1 argument.

Example of the SUMIFS function

The worksheet below displays the sales record of Wamco Int’l in three regions: East, West, and South. Each region is controlled by two sales representatives. The general manager wants to know the number of sales that is 10000 and above made by each regional sales rep for the month under review.

You can use the COUNTIFS function to analyze the data whereby the sales reps and the sales will form the criteria for the count.

The solution is given as follow:

There are two sales reps in each region, hence follow the following steps.

1. Create three columns to represent the East, South, and West as illustrated in the worksheet above.
2. Create six rows to represent each sales rep in each region as shown in the worksheet above.
3. Under the east region and the corresponding cell for Harry Johnson, enter the formula: =COUNTIFS($D$3:$D$58,”Harry Johnson”,$E$3:$E$58,”>=10000″)
4. Copy the above formula and paste it on the eastern region corresponding to Catson Veroni. Change “Harry Johnson” to “Catson Veroni”.
5. Under the south region and the corresponding cell for Uche Golden, enter the formula: =COUNTIFS($D$3:$D$58,”Uche Golden”,$E$3:$E$58,”>=10000″)
6. Copy the above formula and paste it on the south region corresponding to Barry Inkline. Change “Uche Golden” to “Barry Inkline”.
7. Under the west region and the corresponding cell for Philo Metas, enter the formula: =COUNTIFS($D$3:$D$58,”Philo Metas”,$E$3:$E$58,”>=10000″)
8. Copy the above formula and paste it on the west region corresponding to Andre John. Change “Philo Metas” to “Andre John”.

The result shows the total number of sales above 10000 each sales representative was able to achieve in the period under review. Harry Johnson, Philo Metas, and Andre John made the highest number of sales. The southern region made the least number of sales, while Uche Golden did not make any sales 10000 and above.

Conclusion

The COUNTIFS and COUNTIF function in Excel is very useful in data analysis. It helps us to count values based on specified criteria. We can use it to compute templates that will run even in our absence.

The COUNTIF function requires one criterion while the COUNTIFS require multiple criteria.

Haven learned of these functions; you can start implementing them to relevant data. If you encounter difficulties when using them, contact us. Do not forget to check our previous tutorials in this series.

Next week, we shall discuss the AverageIF and AverageIFS functions. Meanwhile, share this with your friends and colleagues.