Learn about the COUNTIF function and its application in Excel.

In Excel, there are several counting functions such as count, counta, countif, countblank, etc., among which the countif function is the most frequently used. The Count function only counts numeric values. The Counta function only counts data that contains text, and the Countblank function only counts blank cells. The Countif function counts based on criteria, so it can be used across a broader range.

I. The general formula of the Countif function

The standard formula for the Countif function is formatted as:

=/+Countif(range, criteria)

Where:

• =/+: Before any formula in Excel, we must start with an "=" or "+" sign. 
• Countif is the name of the function. 
• Range is the area to be counted.
• Criteria is the condition for counting.

II. Counting application of the Countif function

Below is a specific example to help you visualize the Countif function while calculating in an Excel spreadsheet.

Example: Counting male and female students

Range - The data range can be a fixed or non-fixed reference.

In the above example, cell F2 is counting the number of male students, and since the command needs to be copied to cell F3 to count the number of female students, the data range - range is fixed. After copying, we adjust the counting condition - criteria to “female”.

The counting condition - criteria, if in text format, is not case-sensitive but must be enclosed in quotes (“ “).

Example: Counting numbers

In cell D13, we are counting the number of students with a Math score of 8.5 or higher. Conditions related to numbers - criteria, must be enclosed in quotes (“ “).

Example: Counting with multiple criteria (logic Or)

The logic Or means that the object only needs to meet one of several criteria provided to be selected.

In cell C13, we must add several Countif functions together to count both British guests and American guests. Then, the system will understand and count both British and American results.

The criterion - criteria can be written in text form with quotes "USA" or in the form of an Excel cell $C$11.

III. The upgraded version of the Countif function - the Countifs function

The Countifs function is also a conditional counting function like Countif, but it combines multiple conditions across several data ranges.

1. Countifs function formula

=/+Countifs(criteria_range1, criteria1, criteria_range2, criteria2,...)

• The “=” or “+”: one of these signs is mandatory before any formula in Excel.
• criteria_range1 is the first counting range.
• criteria1 is the condition for counting in the first range.
• criteria_range2 is the second counting range.
• criteria2 is the condition for counting in the second range.

The more conditions we have, the more criteria_range and corresponding criteria we have.

2. The Countifs function and logic And

The logic And means that the object must meet all provided conditions to be selected. If it meets only one or a few conditions, it will not be counted.

Example: Counting female students with a Math score of 8.5 or higher

In cell D13, we use the Countifs function to count the number of students who meet both conditions “female” and a test score “>=8.5”. When using the Countifs function as above, the system will automatically count the number of subjects that satisfy all the given conditions by default.

Example: Counting students with a Math score between 6.5 and below 8.5

The Countifs function counts subjects that meet both conditions of having a score both less than 8.5 and greater than or equal to 6.5. The data range 1 - criteria_range1 and data range 2 - criteria_range2 can be the same.

The formula and usage of the Countif and Countifs functions are quite simple. You just need to distinguish when to use the Or logic and when to use the And logic to correctly use the function and formula.