Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use COUNTIFS Function in Excel (4 Suitable Examples)

Microsoft Excel provides various functions to maximize and speed up your productivity. Therefore, today we are going to show you how to use the Excel COUNTIFS function by providing suitable examples. Here is our overview image of using the COUNTIFS function.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Introduction to Excel COUNTIFS Function

Objectives:

  • Counts the number of cells in one or more given arrays that maintain one or more specific criteria.
  • Can be both Array and Non-Array Formulas.

Syntax:

=COUNTIFS(criteria_range1,criteria1,...)

Arguments Explanation:

Argument Required or Optional Value
criteria_range1 Required The first array.
criteria1 Required Criteria applied to the first array.
criteria_range2 Optional The second array.
criteria2 Optional Criteria applied to the second array.

Return Value:

  • Returns the total number of values in the array that maintain all the given criteria.

Version:

  • The DVAR function is available from Microsoft Excel 2010.
  • Here, we will use Microsoft Excel 365.
Notes
  • Only one criterion and one range of values where the criterion will be applied (criteria_range)  is compulsory. But you can use as many criteria and as many ranges as you wish.
  • The criteria can be a single value or an array of values. If the criteria is an array, the formula will turn into an Array Formula.
  • The criteria and the criteria_range must come in pairs. That means if you input criteria_range 3, you must input criteria3.
  • The lengths of all the criteria_ranges must be equal. Otherwise, Excel will raise #VALUE! Error.
  • While counting, Excel will count only those values that satisfy all the criteria.


4 Suitable Examples to Use COUNTIFS Function in Excel

This article will demonstrate to you how to use the COUNTIFS function. Today I will be showing how you can use the COUNTIFS function to count the number of cells satisfying one or more criteria from any range of cells in Excel. Let’s suppose we have a sample data set.

Suitable Examples to Use COUNTIFS Function Excel


Example 1: Using COUNTIFS Function to Count Cells with Value for Single Criteria

To begin with, in this section, we will demonstrate how to count cells with a single criterion by using the COUNTIFS function. So, to know the method,  you can follow the below steps accordingly.

Steps:

  • Firstly, look at the set of data below.
  • Then, we have the students’ records of a school named Sunshine Kindergarten.
  • So, we have the students’ names in column B, and their marks in Physics and Chemistry in columns C and D respectively.
  • Therefore, we want to count how many students got at least 80 marks in Physics.
  • Then, write down the following formula here.
=COUNTIFS(C6:C21,">=80")
  • After that, hit ENTER.

Using Excel COUNTIFS Function to Count Cells with Value for Single Criteria

  • Consequently, you can see that there are a total of 6 students who got at least 80 in Physics.


Example 2: Inserting COUNTIFS Function to Count Cells with Multiple Criteria

In this section, we will demonstrate how to count cells with multiple criteria by using the COUNTIFS function. So, to know the method,  you can follow the below steps accordingly.

Steps:

  • So, let’s try to count cells with multiple criteria.
  • Firstly, we will count how many students got at least 80 in both Physics and Chemistry.
  • Secondly, write down the following formula here.
=COUNTIFS(C6:C21,">=80",D6:D21,">=80")
  • After that, press ENTER.

Inserting Excel COUNTIFS Function to Count Cells with Multiple Criteria

  • As a result, you can see that there are a total of 4 students who got at least 80 in both subjects.


Similar Readings


Example 3: Using COUNTIFS Array Formula for Counting Grades in Excel

In this section, we’ll show you how to use the COUNTIFS array formula to count grades. In order to learn the approach, you can take the steps listed below.

Steps:

  • To begin with, let’s try a different approach.
  • So, let’s try to count the number of students with each grade in Physics.
  • Therefore, for your convenience, I am reminding you of the criteria for each grade.
  • Then, before writing the main formula, see we have made this table in Excel too.
  • After that, we select all the cells in the empty column, enter this Array Formula in the first cell and then press Ctrl + Shift + Enter.
=COUNTIFS(C6:C21,G6:G11,C6:C21,H6:H11)

Using COUNTIFS Array Formula for Counting Grades in Excel

Formula Breakdown
  • If we break down the array formula COUNTIFS(C6:C21,G6:G11,C6:C21,H6:H11), we will find six single formulas.
  • COUNTIFS(C6:C21,G6,C6:C21,H6)
  • COUNTIFS(C6:C21,G7,C6:C21,H7)
  • COUNTIFS(C6:C21,G8,C6:C21,H8)
  • COUNTIFS(C6:C21,G9,C6:C21,H9)
  • COUNTIFS(C6:C21,G10,C6:C21,H10)
  • COUNTIFS(C6:C21,G11,C6:C21,H11)
  • COUNTIFS(C6:C21,G6,C6:C21,H6) returns the total number of cells in the range C6 to C21 that maintain the criteria G6 and H6.
  • Finally, apply the same procedure for the rest of the five formulas.

  • Lastly, you can see that we have got the number of students with each grade in Physics.


Example 4: Applying Excel COUNTIFS Function in Range for Counting Ranks

In this section, we’ll show you how to use the COUNTIFS function in range to count the ranks of students. In order to learn the approach, you can take the steps listed below.

Steps:

  • So, This is the final task today.
  • Therefore, we will try to find out the rank of each student according to their marks in a subject.
  • Then, let’s try it with the marks in Chemistry.
  • After that, select a new column and enter this formula in the first cell of the column. Then press Ctrl + Shift + Enter.
=COUNTIFS(D6:D21,">="&D6:D21)

Applying Excel COUNTIFS Function in Range for Counting Ranks

Formula Breakdown
  • If we break down the array formula COUNTIFS(D5:D20,”>=”&D5:D20), we will find 16 different formulas.
  • COUNTIFS(D6:D21,”>=”&D6)
  • COUNTIFS(D6:D21,”>=”&D7)
  • COUNTIFS(D6:D21,”>=”&D8)
  • COUNTIFS(D6:D21,”>=”&D20)
  • COUNTIFS(D6:D21,”>=”&D6) counts how many values in the array D6 to D21 have values greater than or equal to the value in D6. This is actually the rank of D6.

  • Finally, you can see that we have got the ranks of each student in Chemistry.


Things to Remember

  • When the criterion denotes equal to some value or cell reference, just put the value or the cell reference in place of the criteria.
  • When the criterion denotes greater than or less than some value, enclose the criteria within an apostrophe (“ ”)
  • When the criterion denotes greater than or less than some cell reference, enclose only the greater than or the less than symbol within an apostrophe (“”) and then join the cell reference by an ampersand (&) symbol.
Common Errors with COUNTIFS Function
  • #VALUE shows when the lengths of all the arrays are not the same.

Read More: The Different Ways of Counting in Excel


Conclusion

In this article, we’ve covered 4 suitable examples of how to use the COUNTIFS function in Excel. Therefore, We sincerely hope you enjoyed and learned a lot from this article. Moreover, if you want to read more articles on Excel, you may visit our website, Exceldemy. So, if you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo