How to Use COUNTIF() and Examples


1

COUNTIF () which is one of the Formula formulas contained in a number processing office application (spreadsheet). This formula serves to calculate the total number of cells with certain criteria in meeting the value. So, you don’t need to calculate the amount of data in a range with certain criteria manually anymore.

You can use the COUNTIF() Formula formula in several spreadsheet applications such as Microsoft Excel, WPS Office, Google Sheets, and other applications to count the number of cells with certain criteria to meet values ​​in a particular case. For example, calculating the total number of students who “PASS” in the selection of CPNS entry.

COUNTIF() Formula Form

=COUNTIF(CELL_START:CELL_END,VALUE/CRITERIA) or

=COUNTIF(CELL_START:CELL_END;VALUE/CRITERIA)

For the use of (,) or (;) it depends on the number processing application system that is implemented on the desktop, web, or mobile so you have to be careful in using the (,) or (;) sign in entering values ​​in COUNTIF(). COUNTIF() only focuses on one range. If you are looking for the number of cell data involving more than one range, then you can use the COUNTIFS() formula. However, the author does not discuss the use of COUNTIFS() in this article.

COUNTIF() Usage Example

Here will provide some examples of using the COUNTIF() formula in this article. The author takes the case of student assessment in determining pass or fail in taking the Mathematics exam, there are 10 student data. You can see the use of the COUNTIF() formula in the image that has several examples below.

Example 1:

Counting the total number of students who “PASS” in taking the Mathematics exam manually.

Meaning: Count the amount of data if Cell C2 to Cell C11 is a “PASS”.

Example 2:

Counting the total number of students who “DON’T PASS” in taking the Mathematics exam by taking one of the cells that says “NOT PASS”.

Meaning: Count the amount of data if Cell C2 to Cell C11 is “NOT PASS”.

Example 3

Counting the total number of students who scored 75 in taking the Mathematics exam.

Meaning: Count the amount of data if Cell D2 to Cell D11 is worth 75.

Example 4

Counting the total number of students who scored more than 75 in taking the Mathematics exam.

Meaning: Count the amount of data if Cell D2 to Cell D11 is worth more than 75.

Example 5

Counting the total number of students whose scores are less than or equal to 75 in taking the Mathematics exam.

Meaning: Count the amount of data if Cell D2 to Cell D11 is a value less than or equal to 75.

Example 6

Counting the total number of students who scored greater than or equal to 80 and more than 60 in taking the Mathematics exam. Here there are 2 special criteria to calculate the total number of students in the sheets, namely value greater than or equal to 80 and over 60.

Meaning: Count the amount of data if Cell D2 to Cell D11 is worth more than or equal to 80 and less than 60.

Example 7

Counting the total number of students in student names starting with the letter “A”.

Meaning: Count the amount of data if Cell B2 to Cell B11 starts with the letter “A”.

In the picture above it also applies to calculating the total amount of data that begins with a word or sentence in the cell.

Example 8

Counting the total number of students in student names ending with the letter “O”.

Meaning: Count the amount of data if Cell B2 to Cell B11 ends with the letter “O”.

In the picture above it also applies to calculating the total amount of data ending in a word or sentence in the cell.

Example 9

Counting the total number of students in the name of the student who has the letter “A”.

Meaning: Count the amount of data if Cell B2 to Cell B11 starts and ends with the letter “A”.

In the picture above it also applies to calculating the total amount of data that begins with a word or sentence in the cell.

The author has given several examples above in using the COUNTIF () formula in calculating the amount of data in a range by using certain values ​​or criteria in the Spreadsheet application.

CLOSING

You can calculate the total amount of data in a range by using the COUNTIF() formula in a spreadsheet application. This could be a basic skill in a certification exam related to using the Spreadsheet application or a technical interview before accepting a job involving a Spreadsheet office application.

READ OTHER ARTICLES

Leave a comment

Your email address will not be published.