Cell counting function with data in Excel - Enclose example
In Excel, in addition to performing operations, statistical work after the calculation is quite important. Especially in Excel supports data counting functions that help you to synthesize quickly. The following article introduces data counting functions in Excel.
Cell counting function with data in Excel - Enclose example Picture 1
1. Data counting function Count
Meaning: The function performs counting the number of cells containing the number value.
Syntax : Count (value1, value2, .)
Where: value1, value2 are cell references, or ranges that want to count cells as numeric values.
Attention:
- The Count function counts cells that contain numeric values, dates, or numbers, if cells that are not numeric data types or cannot be translated into numbers will not be counted.
For example:
- Based on the summary of student results at the end of the school year, counting the number of students who have absences during the year.
Due to the number of absences if the number of absences = 0 is empty, the count function can be used. Enter the formula = Count (E4: E17) -> the function will count cells with numeric data types in the holiday column, empty cells the count function ignores:
Cell counting function with data in Excel - Enclose example Picture 2
In case the number of holidays = 0 is not empty, then the number of holidays in a cell has a value of 0, then the count function will count the cells with a value of 0 so in this case it is impossible to use the count function. to count students who are absent from school:
Cell counting function with data in Excel - Enclose example Picture 3
Similar to the date data type, the Count function is similar to the numeric data type:
Cell counting function with data in Excel - Enclose example Picture 4
2. Counta data counting function
Meaning: The function to count the number of non-empty cells will count the number of data cells that have data.
Syntax: = Counta (value1, value2, .)
Where: value1, value2, . are cell references or ranges that you want to count whether or not they contain data.
Attention:
- With the Counta function regardless of numerical or character data, it only cares if the parameters referenced contain data or not.
For example:
- Based on the summary table at the end of the year, the number of students disciplined during the year.
In the notes column, the disciplined student will note the discipline so use the counta function to count the number of cells containing the data in the comment column:
Cell counting function with data in Excel - Enclose example Picture 5
Similarly, you can use the counta function to check the number of students who have entered the final score:
Cell counting function with data in Excel - Enclose example Picture 6
3. Countblank data counting function
Meaning: The function counts the number of cells containing empty data in Excel. The function in contrast to the Counta function counts the number of cells containing data.
Syntax: Countbalnk (range)
Where range is the data range or reference you want to count the number of empty cells.
Attention:
- With the Countbalnk function regardless of numerical or character data, it only cares if the parameters referenced are null values.
For example:
Count the number of students who are not disciplined in the data sheet:
Cell counting function with data in Excel - Enclose example Picture 7
Similarly, you can use the countblank function to count students who do not miss school so that they can give a report rate:
Cell counting function with data in Excel - Enclose example Picture 8
4. Countif data counting function
Meaning: The function performs counting the number of data cells with a given condition.
Syntax: Count (range, criteria)
Inside:
- range: Range of data range you want to count.
- criteria: Counting conditions.
Attention:
- With Countif regardless of whether the data type is a number or a character, its scope is much wider than the count function.
- Only data matching the given condition will be counted.
- In case no value matches the condition => the function returns the value 0.
For example:
Counting the number of good students with an average score of 8.5 or above:
Cell counting function with data in Excel - Enclose example Picture 9
If no value in the data table matches the condition -> the function returns the value 0:
Cell counting function with data in Excel - Enclose example Picture 10
Where the conditional expression is a mathematical comparison you put an expression in double quotes:
Cell counting function with data in Excel - Enclose example Picture 11
5. Data counting function Count
Meaning: The function performs counting the number of data cells that satisfy many given conditions.
Syntax: Countifs ( criteria_range1, criteria1, criteria_range2, criteria2, .)
Inside:
- criteria_range1, criteria_range2, . is the data area containing the data to be counted corresponding to criteria1, criteria2 .
- criteria1, criteria2, . are the counting conditions corresponding to the data regions criteria_range1, criteria_range2, .
Attention:
For example:
- Count the number of students achieving good academic performance without breaking the exam rules:
So there are 2 conditions:
+ Academic performance greater than or equal to 8.5.
+ In the notes column, the corresponding data field has an empty value.
=> Enter the formula: = COUNTIFS (H4: H17, "Good", I4: I17, "") .
Cell counting function with data in Excel - Enclose example Picture 12
Similarly, you can count students quite well without breaking the statute:
Cell counting function with data in Excel - Enclose example Picture 13
The above article helps you learn more about the function of counting cell data in Excel hoping to help you. Good luck!
You should read it
- Conditional counting function in Excel
- How to use COUNTIF function on Excel
- Excel counting functions: Count and Countif
- COUNTIFS function - The function performs counting the number of cells in a data table that satisfy many conditions in Excel
- How to use the Match function in Excel
- CELL function to look up information of a cell in Excel
- How to use the SUBTOTAL function in Excel
- How to name a cell or Excel data area
- Calculate by cell background color in Excel
- DCOUNTA function, how to use the function to count non-empty cells in Excel
- INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function
- How to use ADDRESS function in Excel
May be interested
How to create Header and Footer borders in Word 2019, 2016, 2013, 2010 and 2007
How to use SUMIF with 2 or more conditions in Excel
How to join PDF files with Foxit Reader
How to change the file extension XLSX to XLS to open in Excel 2003
What is a PivotTable? How to use PivotTable in Excel
How to calculate time in Excel: Add and subtract time