Question

Excel COUNT

Answer 1

What is Excel's COUNT function for?
COUNT syntax
COUNT usage example
COUNT and COUNTBLANK: two related functions

---

Excel COUNT

If you usually work with Excel, you know: counting cells by hand without making mistakes is an almost impossible task. But it does not have to be like that. If we only want to know how many cells contain figures, we can use Excel's COUNT function. In a matter of seconds you will know how many cells contain numbers within the range you set. Here we explain how it works..

Index

1. What is Excel's COUNT function for?
2. COUNT syntax
3. COUNT usage example
4. COUNT and COUNTBLANK: two related functions

What is Excel's COUNT function for?

When working with Excel, the need may arise to determine how many cells in a table contain numbers. Sometimes you try to determine how often a result appears. Others, however, seek to count the number of numbers within various values. With the COUNT function, Excel counts only figures, dates, and numbers represented as text in quotation marks .

Note

Excel COUNT only counts the number of cells, the cell values ​​are not included in the calculation. Excel has another function for this: with SUMARIF you can filter the values ​​according to certain criteria to add them..

COUNT syntax

Excel's COUNT formula only needs to be told which range to parse by, among other things, cell references. The colon is used to mark the range from-to . The first value is at the top left and the second at the bottom right.

  =CONTAR(Valor1;[Valor2];?)  

The advantage of this function is that you can enter several ranges at the same time. That is, if the cells Excel should examine are unrelated, you can simply enter multiple ranges without having to reshape the entire table. You can integrate up to 255 additional ranges of different quantities in the COUNT function..

COUNT usage example

The COUNTIF function checks within a specific range for entries that the spreadsheet recognizes as numbers:

  =CONTAR(B2:E2)  

COUNT calculates the number of cells that contain figures.

The formula used in the example determines the number of cells that contain numbers in a row. However, you can also extend the search to the entire table or only take into account certain ranges , in case there are columns in the table that should not be included.

  =CONTAR(B2:E5;G2:J5)  

It is also possible to combine COUNT with other functions . For example, you can calculate an average from the number of cells in the different ranges.

  =PROMEDIO(CONTAR(B2:E5);CONTAR(G2:J5);CONTAR(L2:O5))  

Combine COUNT with other features for more complex tasks.

In this example, cells with numbers within several ranges (independent of each other) are counted and then the average is determined.

COUNT and COUNTBLANK: two related functions

With COUNT you can only count cells that contain numbers. Text that does not correspond to a numeric value or logical values ​​are not taken into account. This is good, since this way the numbers can be differentiated from the rest of the content. However, if you want to include all the values , you can do it with COUNTA. The design of the function corresponds exactly to the COUNT function.

With COUNTA , for example, the meeting attendance rate for a month can be displayed.

  =CONTARA(B2:E5;G2:J5)/32  

With the COUNTA function you can count all cells with content.

In this example, we count all cells with content and divide by the number of available cells. In this case, a person's attendance is marked with an? X ?. If we apply the percentage format to the result cell, we will be able to record the percentage attendance rate.

However, sometimes we want to count all cells that have no content. To do this, the function COUNTBLANK is used in Excel . This function shares the same layout as the other two functions already presented, but can only contain one argument. Therefore, you cannot include multiple ranges in a single function, instead you have to add sums.

  =CONTAR.BLANCO(B2:E5)+CONTAR.BLANCO(G2:J5)  

COUNTBLANK determines which cells have no content.

In this way, if COUNTA and COUNTBLANK are added, the number of all cells in the analyzed range is obtained. This can be very useful, as can be seen if we take the formula for calculating the attendance rate as an example.

  =CONTARA(B2:E5;G2:J5)/(CONTARA(B2:E5;G2:J5)+CONTAR.BLANCO(B2:E5)+CONTAR.BLANCO(G2:J5))  

Adding the two functions gives the number of all cells in a selected range.

In this formula, you no longer have to enter the total number of cells by hand. Instead, you get the sum number from all empty and non-empty cells.

advice

You have more controls in the selection of the criteria with the COUNTIF function. For example, with this function you can search in Excel using certain terms.

---