Getting Most Out of COUNTIF() and COUNTIFS() function in Excel

Introduction:

There are instances when we are interested in counting things. This is true for accountants, engineers, managers and so on. To count, we usually record quantities, either on paper on spread – or if we record on paper, we eventually transfer it to a spreadsheet to make calculations more manageable. COUNTIF() or COUNTIFS() is the function that we use when doing so on spreadsheets.

In today’s post, we will describes the “celebrated” COUNTIF() function and its sister function COUNTIFS() to see how it can help analyses the data when it comes to count.

COUNTIF() Function:

The COUNTIF() function counts for only one criteria and for only one range. This is the definite limitation of the function when a user is willing to count with more than one criterion. But it is not always that we have multiple criteria. The excel help provides following syntax for the function:

COUNTIFS() Function:

This function can take more than one. Hence it a go for choice where we have more than one criterion. It takes consecutive inputs for criteria_range and then criteria and returns the count.

Since most of us are already familiar with the syntax of these functions we will proceed by taking up “Scenarios” where these functions can be used:

THE COUNTIF () FUNCTION WITH EXAMPLES

Accounting – Counting Absents and Presents from attendance recorded:

Record from Personal or administration department is sent to accounts for finalizing the salaries on monthly basis. Accountant are faced with the task of counting or re-validating the presents and absents in the data. COUNTIF function is useful in this scenario where we can count for “P”s and “A”s in the data (see Sheet1A):

The result can be found using the COUNTIF() formula be referring to the ranges and putting up the criteria: “P” and “A” respectively.

In the above formulas the first argument refers to the lookup range where attendance is recorded and the second one is the criteria itself. For absents we count for “A” and for presents we count for “P” thus giving the total count of attendance.

Managing HR Training by using COUNTIF() function:

Companies hire HR people so to manage their HR and staffing needs. In order to make them work efficiently they are given several types of training. Following is a sample report that records training received by human resource staff and various training areas.

The staff that has been training for a topic is marked “D” or done, ones that are still in progress are represented by “IP” or “in progress” and the areas where no training is attended is represented by “-“ (see Sheet2A).

The sheet is populated using the following formula that gives the required results.

Using COUNTIF () function for Inventory Control and Operations:

Manufacturing companies are always looking for a better way to manage their inventory, be it finished one, raw material or inventory in-process. We can use COUNTIF() function to perform simple counts over inventory sheets. The typical way of doing physical count is to use tally marks notation but count if can also be used to count if data is present in a spreadsheet:

The formulas in this sheet are used with logical operator to confirm if the quantity is cross threshold – or a critical level in simple words. This makes sure that we reorder the part well before it hits zero and our operations is interrupted. The following picture (see Sheet3A) shows the result that is obtained.

In the above formula the logical operator “<=” ensures the that COUNTIF() formula counts for values only lower than one set in threshold cells and are presented by values in Column B. Other logical operators that we can use are equal to sign (“=”), greater than or equal to (“>=”), greater then sign (“>”). Additionally we have less than sign (“<”) and not equal to or other then sign (“<>”) as well.

USING COUNTIFS() FUNCTION WITH EXAMPLES:

COUNTIFS() is just a sister function of the COUNTIF() but is more useful when we have more than one traits to count. This function takes multiple criteria and multiple ranges to look up and then return the final count. The argument of the function is already explained in the preceding lines. We will explain it with examples (see Sheet1B):

The example in the above sheet is related to both HR and Accounts department where HR department sent an attendance sheet for reimbursement of training fee claim by a trainer.

The result that we obtained with the formula is shown below:

Another example of using COUNTIFS () could be for the manufacturing case where we want to count for various items bought from a certain manufacturer. In the following table we have listed items along with their supplier and want to know how frequent we have bought certain item for a supplier (see Sheet3B).

In this case we are interested in not individual items but family of item like ALLEN KEY SCREW bought from a certain supplier. To find such a count we will be using the wild cards for search. Wild cards search for other than exact criteria like we are not searching for ALLEN KEY SCREW 5 X 13 but for ALLEN KEY SCREWS only.

This is accomplished when we embed our criteria in asterisks with ampersand to connect it with the lookup criteria. Following table is produced as a result.

Conclusion:

There could be various instances where we can use this COUNTIF() or COUNTIFS() we have mentioned just few. Though these functions are really helpful but they have a limitation as well. The functions are limited by the fact that they only works in columns and if will not work for rows. We have to resort to other functions like SUMPRODUCT or SUM (used as an array formula) to count.

Please download the attached sheet to see how things actually work in spreadsheet.