Excel is the world’s leading spreadsheet program . It has a series of professional and advanced tools to carry out all kinds of data analysis and visualization work.
It is included within the Microsoft Office suite and is available for Windows, MacOS, Android, and iOS . Although it may seem complicated, it really is not. Just take certain basic aspects into consideration and you can start to enjoy its enormous advantages.
In this opportunity, we will explain to you what is related to the counting function that Excel has . Take note!
What is the COUNT function in Excel and what is it for in our spreadsheets?
COUNTIF is an excellent function that is included within Excel, with the objective that the user can know the number of cells that contain numbers, as well as count the numbers within a list of arguments .
It is used mainly to know the number of entries within a field corresponding to a number in a range or matrix of numbers .
For example, you can write the following formula to count the numbers in the range A1: A10: = COUNT (A1: A10) . If three of those cells in the range contain numbers, the result of the formula will be 3.
Also, along with the count function is a COUNTIF call . This is a statistical function that counts the number of cells that meet a criterion . An example of this is when you want to count the number of times that a certain city appears in a customer list.
Steps to apply the COUNT function in my worksheets to know the number of cells in Excel
Due to the difference between COUNTIF and COUNTIF, you must be very careful when using formulas within Excel . The reason is that if you do it incorrectly, you may find an error message.
To avoid this, we explain below the step by step to use the COUNT function within the spreadsheet:
When it comes to the syntax used by the COUNTIF function, you should keep the following arguments in mind:
- Value 1 (Required): first element, which refers to the cell or range in which you want to count the numbers.
- Value 2 (Optional): up to 255 items, additional ranges, or reference cells in which you want to count numbers.
Added to that, you should know that the arguments can refer to a certain variety of different types of data, but only the numbers indicated in them are counted.
In this sense, the procedure to follow to use COUNT within Excel is:
- Enter in the spreadsheet that contains the information you need.
- Write the formula = COUNT (value1: value2) .
- Hit enter .
In case you want to include the number of cells and a certain value in the count, the formula is = COUNT (A1: A5,2) . With it, the number of cells that contain numbers in cells A1 to A5 is counted, and the value 2.
This function is ideal when you want to count, but conditional on a certain criteria . A clear example of this is if you have a list of values and you want to count those greater than 10. The syntax for this function is = COUNTIF (range; “criteria”) .
The range is the number of cells that you want to count conditionally, while the criterion corresponds to those cells where a certain criterion is met .
Some criteria can be:
- Greater than
- Smaller than
- Equal to
- Less than or equal to
The correct way to apply the formula in Excel is as follows:
- Enter the spreadsheet where the data you need is.
- Apply the formula indicating the range . Then write the criteria you require.
- Click Enter .
Aspects to consider in the COUNT function
There are certain details that you should know when you are going to work with the COUNT function .
First, when it comes to COUNTING alone, there are a number of observations to consider:
- Arguments are counted, that is, numbers, dates, or a text representation of the numbers (for example, a number in quotation marks such as “2”).
- Logical values and textual representations of numbers written directly in the argument list are taken into account .
- Arguments that are from error values or texts that cannot be translated into numbers are not counted .
- In the case that an argument is a matrix or a reference, only the numbers of said matrix or reference will be considered . Empty cells, values or error texts are not included in the count.
- If you want to count logical values, error values, or text, use the COUNT function .
- If you want to count only numbers that meet a specific set of criteria, use COUNTIF or COUNTIF .
Regarding the specific function of COUNTIF, there are a series of recommended practices that facilitate your work.
Here we show you:
- Both uppercase and lowercase letters are ignored in text strings . This means that if there is a string corresponding to “PEAR” or “pear”, the result of the number of cells will be the same.
- Take advantage of wildcard characters . Some of them are the question mark “?” that replaces any single character, and the asterisk “*” that replaces any sequence of characters.
- Make sure that the data does not contain incorrect characters, that is, pay careful attention that the data does not contain spaces at the beginning or the end, that there is no inconsistent use of straight or typographic quotes, etc.
- To make your work more organized, use named ranges .