Login

+4 votes

Best answer

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

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 ** .

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..

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..

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

` `` =CONTAR(B2:E2) `

** 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)) `

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 `

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) `

** 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)) `

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.