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.
Submit feedback
Your email address will not be made public. Fields marked are required *
Search
Trend
-
What is Black Myth: Wukong? Detailed Guide on System Requirements and Gameplay
08-21-2024 . 694 view
-
Call of Duty: Black Ops 6 - Intense, Mysterious, and Surprising Warfare
09-02-2024 . 640 view
-
Casio WS-B1000: The Perfect Blend of Traditional Watch and Smartwatch
08-11-2024 . 547 view
-
The "End of Life" for Windows 10: A Massive E-Waste Threat and Sustainable Solutions
08-18-2024 . 520 view
-
Call of Duty: Black Ops 6 Consumes 300GB SSD: Xbox Series X/S Gamers Take Note
09-02-2024 . 508 view
0 feedback