COUNT, COUNTA, COUNTIF, COUNTBLANK and COUNTIFS formulas in Ms. Excel

In calculations with the Microsoft Excel application, of course you have encountered or even used several functions such as: Count, Counta, Countif, Countblank and Countifs.

Excel formulas COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK are functions (functions) that are used to calculate the amount of data (COUNT). The formula using this formula is almost the same as making the COUNT formula. Here are each of the uses of the formula.

1. COUNT function

Count is used to calculate the number of data owners in the form of numbers only. Of course, it is different from using the SUM function which is used to calculate the amount of data.

Writing the formula / COUNT formula is = COUNT (Initial Cell: End Cell)

The starting cell to the end cell as of writing above is a range (a collection of several cells), thus, writing the COUNT formula can also be written as below: =COUNT(range)

2. COUNTA function

Counta is used to calculate the amount of data just like COUNT, but COUNTA can calculate data types in the form of numbers / numerics, text and others.

Writing the COUNTA formula / formula is = COUNTA (Initial Cell: End Cell)

Atau bisa juga dituliskan =COUNTA (range)

3. COUNTIF function

Countif is used to calculate the amount of data with certain criteria or conditions. In countif, data types that can be calculated can be in the form of numbers / numerics, text, and others.

Writing the formula = COUNTIF (range: criteria)

You can see an overview of the three functions above in the following questions:

Explanation: In the picture above, there are 5 data, which consists of 3 data in the form of numbers, namely: 10, 13 and 15. Then there is one text, namely: letter A, and finally there is one character &.

The result of working using the COUNT formula is 3, because COUNT can only count numeric data types. To work with COUNTA yields 5, because all existing data types can be accommodated by the COUNTA function. For COUNTIF only returns 1, because the criterion is taken by the & (amperand) character or operator.

In the article above, of course you better understand how to use and differentiate the COUNT, COUNTA and COUNTIF functions.

4. COUNTBLANK function

COUNTBLANK is used to count the number of blank cells in a predetermined range.

Writing the formula / formula: = COUNTBLANK (range)

Gambar dibawah ini merupakan contoh pengerjaan soal menggunakan fungsi counta, countif dan countblank.

Explanation: To calculate the number of respondents who participated in the “BeritaOkeBanget Talk Show” survey, you can use the COUNTA function by taking data from Gender, the result as you can see in the picture above is 7.

Why use the COUNTA function instead of COUNT? Of course, you can’t use the COUNT function because the data type is Text instead of Number. For the number of respondents who gave good comments, the calculation result is 3, you can see the use of the formula for good criteria = COUNTIF (D4: D10, “Good”).

Still using the COUNTIF function for Kritera, only 2 people answered and 1 person answered Not good. Meanwhile, the number of respondents who did not comment in the survey (did not answer / empty) was 1 person.

5. COUNTIFS function

COUNTIFS is used to calculate the amount of data with certain criteria. Of course it is different from COUNTIF which only uses one criterion / condition.

Writing the formula / formula: = COUNTIFS (critera_range1, criteria1, [critera_range2, critera2], …)

See the work on COUNTIFS below:

Explanation: For the number of women with Good comments, you can simply type the formula = COUNTIFS (C4: C10, “P”, D4: D10, “Good”) the result is 3. The writing of COUNTIFS uses 2 criteria / conditions where there are 3 sexes of women ( P) who answered Good. This, of course, is in accordance with the writing of the formula. = COUNTIFS (critera_range1, criteria1, [critera_range2, critera2],…)

Where:

  • C4:The C10 is the first critera range..
  • “P” is the first critera
  • D4:D10 is the second range criterion
  • “Good” is the second criterion.
  • For the number of women with sufficient comments the result is 1, the formula is = COUNTIFS (C4:C10,”P”,D4:D10,”Enough”)
  • The number of men with good comments is 1 the formula is = COUNTIFS (C4:C10,”L”,D4:D10,”Very nice”)
  • The number of men with sufficient comments results in 2 the formula is = COUNTIFS (C4:C10,”L”,D4:D10,”Enough”)

That’s the tutorial on How to Use Formulas in the COUNT, COUNTA, COUNTIF, COUNTBLANK and COUNTIFS Functions. Hopefully this is useful.

Leave a Reply

Your email address will not be published. Required fields are marked *