COUNTIF Function in Excel

With the advent of the COUNTIF function in Excel, users were able to quickly and efficiently count cells that satisfied particular criteria, eliminating the need for complicated calculations or manual counting. In 1985, Microsoft launched the first version of Excel, with the in-built COUNTIF function.

Users can use the function to count cells in a range that satisfy specified criteria, such as cells with a specific value or cells that meet a given condition. The COUNTIF function immediately rose to prominence as one of Excel’s most popular and commonly used functions. It has been updated and enhanced over time, with new capabilities introduced to handle more complicated criteria and ranges. 

What is the COUNTIF Function in Excel?

Spreadsheets are essential in several sectors of enterprises for calculating effectively and precisely. The COUNTIF function in Microsoft Excel counts the total number of cells inside a range that fulfill a set of criteria. 

As the title implies, the COUNTIF function combines the “Count” and “IF” formulas together. It is an Excel built-in function classified as a Statistic Operation and accessible in Excel as a worksheet tool (WS). 

In simple terms, COUNTIF Excel counts cells across a number of ranges according to one or more conditions. It incorporates conditional logic into the equation to count those cells that fulfill particular requirements. 

With an advanced excel course, you can learn in-depth about the various functionalities of Excel.

Syntax of COUNTIF Excel

This COUNTIF function’s syntax contains a list of target cells accompanied by a single term. The function accepts logical operators as well as wildcards. You can use these two to broaden or narrow down your criteria.

The COUNTIF Excel function has the syntax:

COUNTIF, in its most basic form, says:

=COUNTIF(Where do you wish to look? What are you looking for?)

Keeping syntax correct in any code is critical, but this is particularly relevant when interacting with Excel formulas. The correct syntax prevents the cell from generating a null value and allows your worksheet to function as planned. 

The function can count cells that contain dates, numerals, or text. COUNTIF(A1:A10, “Donald”), for instance, counts the number of cells in the range A1:A10 containing the phrase “Donald.” It is important to know that the COUNTIF equation always returns a positive integer that’s either zero or non-zero.

Advantages of COUNTIF function in Excel

The COUNTIF formulas serve a variety of functions, critical to business operations. The following are some of the primary benefits of using the COUNTIF function in Excel:

1. Category Outline: The ability to present visitors with an extensive description of the condition of a specific category is the first significant advantage of utilizing COUNTIF in Excel. Rather than requiring a detailed examination of each cell inside a given field, the COUNTIF formula determines the status of a number of metrics. This not only preserves time for the management squad, but it is also more efficient.

2. Monitoring Staff Performance: Monitoring staff efficiency is critical because weak employees lead to poor business practices and failed initiatives in the long run. COUNTIF functions are useful for achieving positive results in this regard. Tracking weekdays below and over specific criteria provides a glimpse into the achievements and failures of employees across the entire organization.

3. Dating Purchases: Aside from the number of goods accessible, the date your clients bought them is critical. If the goods are time-sensitive, surpassing the expiration date is not only negative for the business’s image but also harmful to the well-being of the consumers. COUNTIF methods assist date verification and automated security processes, which is especially crucial in the food business. Microsoft has made significant investments to preserve Excel as the ruler of business intelligence, ensuring that it is functionality rich while also being user-friendly.

How to Apply COUNTIF Formula in Excel?

Let’s understand how to use the COUNTIF function in Excel when they become particularly useful in the workplace.

Here’s an example of using the COUNTIF function to determine how many “A” John seems to have in his tests. The image below depicts his results.

Case 1: Calculating the Total Number

You’re now tasked with estimating how many “A”s John has in total from his results.

1. First, choose a blank cell to enter the result into.
2. Then enter the following formula: =COUNTIF(B2:I13,”A”).
3. Enter the COUNTIF Formula.

You may also enter the following formula: =COUNTIF(B2:I13, B2), since “B2” has the value “A.”

4. Then, on your keyboard, push the “Enter” button and  you’ll notice that John’s scores have 34 “A.”

Case 2: Calculating the Total Count of Each Test or Subject

Alternatively, if you would like to determine the total number John has earned in each subject as well as the numbers for each test, you may employ this method.

1. In this illustration, the cell “B14” was chosen.
2. Enter the formula: =COUNTIF(B2:B13,”A”).
3. Hit the button “Enter”.

4. Then move the pointer to the cell’s bottom-right corner. 

5. Place the cursor in the corner and drag using your mouse to see what the image depicts.

6. Release the hold on the mouse. The outcome is subsequently observed. And you finally realize how many “A”s there are in each subject.

The stages for each test follow the same procedure with the exception of the area.

Case 3: Calculating the Total Number of Cells in a Given Range

You are going to have the numbers for the marks greater than 90 in the graphic below.

In this case, you may also utilize the COUNTIF Excel function.

1. Choose a cell that is empty.
2. Enter the following formula: =COUNTIF(B2:D13,”>90″).
3. Tap the key “Enter.” You will then be able to see the outcome 4. 

Furthermore, you can modify the parameters to achieve your objectives.

COUNTIF Function in Excel with Multiple Criteria

The Excel technique for counting several criteria[1] is =countifs (). The “s” at the end renders it plural, implying that there are numerous criteria, two or more. COUNTIFS operates similarly to COUNTIF, except that you can include extra criteria in the calculation.

It is important to note that:

• The criteria of each range are implemented one cell at a moment. The count grows by one if all of the initial cells match their corresponding conditions. If all of the second cells fulfill their corresponding requirements, the count rises by one more time, continuing in this manner till all of the cells have been checked.
• The COUNTIFS function considers an empty cell as a 0 value if the criteria parameter is a pointer to an empty cell.
• In the set of criteria, you are able to incorporate the wildcard characters such as question mark (?) as well as an asterisk (*). An asterisk recognizes any characters in a sequence, while a question mark identifies any single character. Use a tilde (~) before the character to identify an actual asterisk or question mark.

COUNTIFS is useful for performing quick financial analyses. For instance, we are provided a set of duties that must be done by a department, as well as the due dates and urgency of each job. By using the COUNTIFS function, we can create a table that shows the date and count of each job along with its priority.

Consider the following example to better understand the function’s applications. Let us suppose we are handed the following information:

We need to know how many goods are in stock, which means the value in the C column has to be greater than 0 but has yet to be sold means the value in the D column should be 0.

As we can see, the only item where no stock has been sold is Brown Bread. 

Thus, the end result we get is:

You will gain a better knowledge of the manner in which the COUNTIFS algorithm works after reviewing the above evaluation. Additionally, you may create as many as 127 ranges along their associated criteria. 

COUNTIF and COUNTIFS: What’s the Difference?

The COUNTIFS function is identical to the COUNTIF Excel function, with the exception that it counts the total number of cells that are in a range fulfilling multiple conditions. COUNTIFS must meet all of the conditions in order to measure that particular value. 

Hence, adopting logical formula logic, all criteria provided must compute to be true in order for a segment to be recorded.

	COUNTIF Function	COUNTIFS Function
1	The COUNTIF Excel function only accepts one range and one criterion.	The COUNTIFS function, on the other hand, accepts several parameters to be applied in different ranges.
2	The COUNTIF function does not support multiple criteria. We can use several COUNTIF methods, but it outputs all the cell numbers for every condition and then aggregates them.	The COUNTIFS function supports multiple criteria. We simply enter numerous conditions to be applied in different ranges.
3	COUNTIF solely counts non-blank character cells; any enforced constraint or specific text is not supported.	The COUNTIFS method counts the same textual non-blank cells as the COUNTIF function, yet it additionally allows you to match specific content within text cells.
4	The COUNTIF function identifies a fragmented text when we insert the text in the equation using wildcards (i.e., *). Additional wildcards include a question mark (? ), an asterisk (*), a, and a tilde ().	COUNTIFS compares the text in the same way as COUNTIF would, but it requires more texts to meet the criterion. When we combine these, we get a match that is particularly beneficial for searching items in large datasets.
5	The COUNTIF Excel function simply calculates the cells that are present within a single range with a single parameter collection.	For determining the number of cells for several conditions set inside single or multiple ranges, the COUNTIFS function comes in handy.

The two Excel functions, COUNTIF and COUNTIFS, vary in that COUNTIF is meant to count cells with a specific and single condition in a single range, while COUNTIFS can evaluate several criteria within the same or separate ranges. 

When to Use the COUNTIF Excel Function?

Employ the COUNTIF Excel function when you’re only assessing a particular range including a single parameter. This command just computes the unique range and condition you specify for your dataset. 

=COUNTIF(range, “criterion”) 

The range for the COUNTIF function in Excel comprises one row or column, while the requirement is one integer, textual, or expression result. For smaller quantities of data, this operation is often preferable to the COUNTIFS function.

When to Choose the COUNTIFS Function?

The COUNTIFS function monitors the number of cells within the identical or distinct ranges that fulfill single or several criteria. For assessing several data sets and predefined criteria within a single worksheet, COUNTIFS is useful.

COUNTIFS, for instance, can be used by financial analysts to manage and assess numerous procedures such as team projects or budgeting operations. Analysts can use the method to count the number of specified things according to a variety of parameters such as dates or hours. 

Conclusion

There are several statistical techniques in Microsoft Excel, such as the COUNTIF Excel function. This function can be used for a range of data analysis and classification, such as sorting and assessing performance, profitability, and efficiency indicators. The COUNTIF formula can help make datasets more readily available and accessible.