One problem most users face while dealing with a massive volume of data is to count the number of items that fulfill specific criteria. You may also need to find the number of items that satisfy more than one criterion. While there are many ways to do that, I will be talking about how to do it using the COUNTIF function available in Microsoft Excel or any other popular spreadsheet program. 

You already have an idea from the function’s name, which combines two words, COUNT and IF. So, what COUNTIF does is it COUNTS only IF the condition is fulfilled. Like that, COUNTIFS do the same thing, but as it is plural, it means you can feed multiple conditions. So, let’s find out how to use it.

So, without any further delay, let’s get started with how to use the COUNTIF function on Microsoft Excel or any other popular spreadsheet program.

Using the COUNTIF function in Excel

Before getting started, the format for the COUNTIF function goes as follows.

=COUNTIF(range,criteria)

Well, to make things simpler, let’s look at a dataset containing data on multiple employees in a company, including details such as their name, department, job title, gender, hometown, etc.

Now, I want to count the number of employees in the finance department. You may also want to know the number of employees working in the IT department. Requirements can vary, but you get my point.

Whatever the case, let’s keep the department’s name in cell S14, and the number of employees in that department will be in S15.

The dataset’s departments’ names are in column D, the first record is in the second row, or our relevant data starts from cell D2. So, the formula in cell S15 will go as follows.

=COUNTIF(D2:D999,S14)

Here, the text entry in cell S14 will be used as a criterion to count the number of records that fulfill the requirements. If you want a text entry, you can also do that. For example, instead of S14, you can directly write “Sales,” the function will go as follows.

=COUNTIF(D2:D999,“Sales”)

Using the COUNTIFS function in Microsoft Excel

This will return the number of employees in the Sales department.

Now, if you want to club multiple criteria and increase the counter by one if all the criteria match, we will use the COUNTIFS function. It works the same way as the COUNTIF function, except that we can only enter ‘n’ number conditions.

Before going further, the formula for the COUNTIFS function goes as follows.

=COUNTIFS(range1,criteria1,range2,criteria2,...,range n,criteria n)

Using the above datasheet, let’s say we want to organize an engineering workshop in Miami. We need to know the number of employees in the engineering department so we can make the necessary arrangements for all of them.

We want the number of employees in cell S30, the city in column M, and the department in column D, in cells S28 and S29, respectively.

So, the formula in the cell S30 will go as follows.

=COUNTIFS(M2:M999,S28,D2:D999,S29)

Similarly, it is possible to replace the cells S28 and S29 with absolute text strings like Miami and Engineering, as it is the requirement in our case.

So, in that case, the formula will go as follows.

=COUNTIFS(M2:M999,“Miami”,D2:D999,“Engineering”)

Dealing with numbers in COUNTIF or COUNTIFS

It is also possible to deal with numbers in COUNTIF or COUNTIFS. For example, if we need to find the number of employees older than 50, that is also possible.

If we want the count to be in cell R6, the formula will be as follows. As the age is in column H, the function in cell R6 will be as follows.

=COUNTIF(H2:H999,">50")

If we want fewer employees than 50, here’s the function.

=COUNTIF(H2:H999,"<50")

If we want the number of employees with an age more than or less than 50, i.e., not equal to 50, the function is below.

=COUNTIF(H2:H999,"<>50")

So, COUNTIF and COUNTIFS are powerful functions in Microsoft Excel, making it easy to carry out conditional countings in different setups. COUNTIFS can do more, and you can learn them once you start using them regularly.

Do you have any further questions regarding the COUNTIF and COUNTIFS functions? Feel free to comment on the same below.

 

Other Articles:

• How to create data bars in Microsoft Excel for numeric values
• Calculate age with Google Sheets, Microsoft Excel, or any other spreadsheet program
• How to dynamically adjust column width in Microsoft Excel based on cell contents
• How to calculate a loan EMI on Google Sheets, Microsoft Excel and other spreadsheet program