Buzz
In Excel, besides performing calculations, data analysis after computation is quite important. Especially in Excel, there are functions for counting data to help you summarize quickly. This article introduces counting functions in Excel.
1. Count Data Function
Purpose: This function counts the number of cells containing numerical values.
Syntax: Count(value1, value2,…)
Where: value1, value2 are cell references or ranges you want to count cells with numerical values.
Attention:
- The Count function counts cells containing numerical values, dates, or numeric text. Cells not in numeric format or unable to be parsed as numbers will not be counted.
Example:
- Based on the summary table of end-of-year student results, count the number of students who have missed school during the year.
Since the number of absences is left blank if the student did not miss any days, you can use the count function to count the number of absent students. Enter the formula =Count(E4:E17) -> the function will count cells with numerical data in the absence column, ignoring blank cells:
In cases where the number of absences equals 0 and the data is not left blank, indicating the student has not missed any days, the count function will count cells with a value of 0. In this case, the count function cannot be used to count the number of students who missed school:
Similarly, for date data type, the Count function counts similarly to numerical data:
2. Counta Data Function
Meaning: The function counts the number of non-empty cells, meaning it counts cells that already contain data.
Syntax: = Counta(value1, value2,…)
Where: value1, value2, … are cell references or ranges you want to count whether they contain data or not.
Attention:
- Counta function does not differentiate between numerical or character data, it only cares whether the referenced parameters contain data or not.
Example:
- Based on the year-end summary table, count the number of disciplined students throughout the year.
In the note column where disciplinary actions are recorded, use the counta function to count the cells containing data in the note column:
Similarly, you can use the counta function to check the number of students who have entered their final grades:
3. Countblank Data Count Function
Meaning: The function counts the number of cells containing empty data in Excel. It is the opposite of the Counta function, which counts the number of cells containing data.
Syntax: Countbalnk(range)
Where range is the data range or reference in which you want to count the number of empty cells.
Attention:
- Countblank function does not differentiate between numerical or character data, it only focuses on whether the referenced parameters are empty values or not.
Example:
Count the number of students not disciplined in the data table:
Similarly, you can use the countblank function to count the number of students who did not miss class to provide a report ratio:
4. Countif Data Function
Meaning: This function counts the number of data cells with a specified condition.
Syntax: Count(range, criteria)
Where:
- range: Data range for counting.
- criteria: Counting condition.
Attention:
- Countif function does not differentiate between numeric and text data types, and its range is much broader than that of the count function.
- Only the data that meets the given condition will be counted.
- If no value meets the condition => the function returns 0.
Example:
Count the number of high-achieving students, meaning those with an average score greater than or equal to 8.5:
If no value in the data table satisfies the condition -> the function returns 0.
In case the condition expression is a mathematical comparison, you put the expression within double quotes:
5. Count Data Function
Meaning: This function counts the number of data cells that meet multiple predefined conditions.
Syntax: Countifs(criteria_range1, criteria1, criteria_range2, criteria2,…)
Where:
- criteria_range1, criteria_range2, … represent the data range containing the data to be counted corresponding to criteria1, criteria2…
- criteria1, criteria2, … are the counting conditions corresponding to the data ranges criteria_range1, criteria_range2, …
Note:
Example:
- Count the number of students achieving excellent academic performance without violating exam regulations:
So there are 2 conditions:
Achievement level is greater than or equal to 8.5.
In the notes column, the corresponding data field value is empty.
=> Enter the formula: =COUNTIFS(H4:H17,'Excellent',I4:I17,'').
Similarly, you can count the number of students who are good without violating regulations:
The article above helps you explore more about counting functions in Excel, hoping to assist you. Wishing you success!
