How to Use COUNTIFS() and Examples


5

In the previous article, we discussed how to use the COUNTIF() formula which can only count the number of data cells by focusing on one range with certain assessments or criteria. For this time, the author discusses how to use the COUNTIFS() formula in Spreadsheets.

This tutorial applies to office applications that support Spreadsheets (number processors) such as Microsoft Excel, WPS Office Spreadsheets, Google Sheets, and other applications using the COUNTIFS() formula.

COUNTIF() Formula Form

=COUNTIFS([CELL_AWAL:CELL_AKHIR,NILAI1/KRITERIA1],[CELL_AWAL:CELL_AKHIR,NILAI2/KRITERIA2],

[…])

or

=COUNTIFS([CELL_AWAL:CELL_AKHIR;NILAI1/KRITERIA1],[CELL_AWAL:CELL_AKHIR;NILAI2/KRITERIA2],

[…])

For the use of (,) or (;) it depends on the number processing application system implemented on the desktop, web, or mobile so you have to be careful in using the (,) or (;) sign in entering values ​​in COUNTIFS(). COUNTIFS() can focus on one or more ranges in counting lots of data with certain criteria.

COUNTIFS() Usage Example

Here will give some examples of using the COUNTIFS() formula in this article. The author takes the case of student assessment in determining whether to pass or not pass the exam in several subjects for which there are 10 student data.

You can see the use of the COUNTIFS() formula in the image with several examples below.

Example 1

Counting the number of students who graduated with an average score of 80 and above.

=COUNTIFS(H3:H12;”PASS”;G3:G12;”>=80″)

Meaning: Count the amount of data if Cell H3 to Cell H12 is a PASS and Cell G3 to Cell G12 is a value above 80.

Example 2

Counting the number of students who get Mathematics and Science scores below 70.

=COUNTIFS(E3:E12;”<70″;F3:F12;”<70″)

Meaning: Count the amount of data if Cell E3 to Cell E12 is a value below 70 and Cell F3 to Cell F12 is a value below 70.

Example 3

Counting the number of students who “DO NOT PASS”. For COUNTIFS() this can be used to count the amount of data involving only one range on certain criteria.

=COUNTIFS(H3:H12;”DO NOT PASS”)

Meaning: Count the amount of data if Cell H3 to Cell H12 is NOT PASSED.

Example 4

Counting the number of students whose Indonesian language scores are above or equal to 80 and Mathematics scores below 80.

=COUNTIFS(C3:C12;”>=80″;E3:E12;”<80″)

Meaning: Count the amount of data if Cell C3 to Cell C12 is a value above or equal to 80 and Cell E3 to Cell E12 is a value below 80.

Example 5

Counting the number of students whose Science Language scores are above or equal to 70 and the average value is below 80.

=COUNTIFS(F3:F12;”>=70″;G3:G12;”<80″)

Meaning: Count the amount of data if Cell F3 to Cell F12 is a value above or equal to 70 and Cell G3 to Cell G12 is a value below 80.

Example 6

Counting the number of students who “PASS” but the English score is below or equal to 75.

=COUNTIFS(H3:H12;”PASS”;D3:D12;”<=75″)

Meaning: Count the amount of data if Cell H3 to Cell H12 is a PASS and Cell D3 to Cell D12 is a value below or equal to 75.

Example 7

Counting the number of students whose Mathematics score is 85, science value is above or equal to 80, and the average value is below or equal to 80. Here make 3 assessments or criteria.

=COUNTIFS(E3:E12;85;F3:F12;”>=80″;G3:G12;”<=80″)

Meaning: Count the amount of data if Cell E3 to Cell E12 is worth 85, Cell F3 to Cell F12 is a value above or equal to 80 and Cell G3 to Cell G12 is a value below 80.

Example 8

Counting the number of students starting with the letter G who got a Math score of 85.

=COUNTIFS(E3:E12;85;D3:D12;”G*”)

Meaning: Count the amount of data if Cell E3 to Cell E12 is worth 85 and Cell D3 to Cell D12 starts with the letter G.

The author has given several examples above in the use of the COUNTIFS () formula in calculating the amount of data in one range or several ranges using certain values ​​or criteria in the Spreadsheet application.

Finished.

CLOSING

You can calculate the total amount of data in one or more ranges by using the COUNTIFS() 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.