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!

4 ★ | 2 Vote

May be interested

  • How to create Header and Footer borders in Word 2019, 2016, 2013, 2010 and 2007Photo of How to create Header and Footer borders in Word 2019, 2016, 2013, 2010 and 2007
    create borders header, footer in word to make your word document more beautiful and professional. if you do not know how to create header and footer borders in word, please refer to the following article to know 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 ExcelPhoto of How to use SUMIF with 2 or more conditions in Excel
    you already know the sumif function to calculate conditional sum in excel, but you do not know how to use the sumif function with 2 or more conditions in excel. so you let tipsmake.com find out in this article.
  • How to join PDF files with Foxit ReaderPhoto of How to join PDF files with Foxit Reader
    how to join pdf files with foxit reader. unlike the word document format that can be easily edited, copied, and pasted, the pdf is divided into pages and we can only stitch pages together. to join the pdf file, we will use foxit phantom software.
  • How to change the file extension XLSX to XLS to open in Excel 2003Photo of How to change the file extension XLSX to XLS to open in Excel 2003
    how to change the file extension xlsx to xls to open in excel 2003. you have a file in .xlsx format but the computer needs to open and install only excel 2003? don't worry, just follow the simple .xlsx to .xls extension instructions as below, you will get what you want.
  • What is a PivotTable? How to use PivotTable in ExcelPhoto of What is a PivotTable? How to use PivotTable in Excel
    excel's pivottable is a great tool that microsoft equips into excel so users can summarize, analyze, learn and present data more easily. what is a pivottable? how to use pivottable in excel
  • How to calculate time in Excel: Add and subtract timePhoto of How to calculate time in Excel: Add and subtract time
    excel can calculate the amount of time by adding / subtracting method to give you the result of the amount of time in minutes, hours, days, and months. the following software tips will guide you the most basic formulas. the problem is that you have time to complete two steps of work, and you want to know how many hours and minutes it will take to complete the job.