If you are familiar with Microsoft Excel, you should know that it has a large number of functions that allow you to carry out a large number of calculations, actions and much more. These are the main tools of the programs that are created in Excel, including the creation of macros, to perform a specific task. One of them is the ‘COUNT IF’ function, which is an important base when you want to make comparisons and search for outstanding data in a list. That is why this time we will explain how to use the ‘COUNT IF’ function in Excel , so that you can use it easily and quickly.
Index( )
- What is Excel’s ‘COUNTIF’ function used for?
- What is the correct syntax for this function?
- To count cells greater than zero
- With formula
- Using Kutools
- How can you count cells less than zero?
- Can cells with non-zero values be counted?
- Other useful functions for accounting
- ADD and SUBTRACT functions
- PERCENTAGE function
What is Excel’s ‘COUNTIF’ function used for?
Basically the ‘COUNTIF’ function is used to determine the data that stands out in a list of data by a previously established characteristic . When breaking down the functions that intervene in this, it can be seen that it takes into account the ‘IF’ function, which is logic and the ‘COUNT’ function, which is statistical.
An example of its use is to have a list of names, surnames, addresses, heights and ages of a group of people. If you want to know which people are greater than 1.80 meters, then you can use the ‘COUNTIF’ function to find all those who meet this characteristic.
Another example would be to search for all registered persons who are of legal age, using the age field to make the comparison. And so in this way it is that this function can be used, fulfilling a task that is generally widely used in business areas .
Likewise, this function can be used in a macro as part of an isolated process that complements the purpose of the program. For this you must know how to enable or disable macros in Excel .
What is the correct syntax for this function?
It is important to be aware that Excel has its own programming language , so if the correct syntax is not used, the function we want will not be able to be executed. In the case of the ‘COUNTIF’ function, the correct way to use it is as follows:
- In a cell, the equal sign ‘=’must be written in a mandatory way because in this way Excel recognizes that the following must be considered a function.
- The full syntax is: ‘= COUNTIF ( range, criterion )’ .
It should be noted that the quotation marks should be ignored , and in the same way when placing the equal sign you can see that a list of functions is displayed. As we write the name of the function, it will appear in the list, so that we write it correctly.
Now, we know the correct syntax but we also need to know how to enter the function parameters. We start with the range in which we will have to establish the group of cells that will be part of the study of the function . In this field you must enter the starting cell and the ending cell where all the data to be considered are located.
- The correct syntax would be: ‘= COUNTIF ( A1: A10; criterion)’ .
In the previous case, it can be noted that the range is established from cell A1 to cell A10 where all the study data must be. Here we explain how the criterion parameter should be entered .
- This field refers to the value against which all the data in the range will be compared.
- You can place logical expressions suchas: greater than, less than, equal greater than, equal less than, cells with specific data, and more.
- Logical expressions, words, number, and more must be enclosed in quotation marks (“”)while the cell does not.
- An example would be: ‘= COUNTIF ( A1: A10; ” > 1.80″ )’ . In this case we ask which values in the range of cells A1 to A10 are greater than 1.80, referring to the previous example.
In the case of wanting to search for a specific value of a cell, it should be placed as follows: ‘= COUNTIF ( A1: A10 ; B2 )’ . In this way we would be looking in the established range for what is inside cell B2.
On the other hand, comparisons can also be made directly with cells if the value to be compared is not specifically known . Only the following syntax should be used for this:
- ‘= COUNTIF ( A1: A10; “>” & B2 )’ .
In this case, it would be looking for all the values within the range that are greater than the value within cell B2 . You can use the special symbol ‘&’ to concatenate data from other cells in your search criteria.
To count cells greater than zero
Now that we know the correct syntax for the ‘COUNTIF’ function we can start creating a lot of comparisons and finding the values that match the search criteria. In this case we will explain how to count cells greater than zero .
With formula
As we explained previously, to count cells greater than zero , the following syntax must be used using the special comparison characters:
- ‘= COUNTIF ( data range; “> 0” )’ .
We simply have to set the data range where we want to find the cells whose content is greater than zero and that’s it. The result can be seen in the cell where we write the function. You can easily make a histogram in Excel using functions like this.
Using Kutools
Kutools for Excel is an add-in that helps us to further simplify Excel functions . Its purpose is to transform complicated tasks in just a couple of clicks. In this case we want to know how to count cells greater than zero, and for this we do:
- First of all, you have to download and install Kutools for Excel.
- We will go to the ‘Kutools’section on the toolbar and follow this path: ‘Kutools’ > ‘Select’ > ‘Select specific cells’ .
- A pop-up window will appear. The next thing will be to select the range of cells with the pointer taking into consideration the type of selection: ‘cell’, ‘complete row’ or ‘complete column’.
- Next, in the ‘Specific type’ partwe will have to select ‘Greater than’ or ‘Greater than’ .
- In the box just to the right we will have to place the value to compare, in this case the ‘0’.
- Then we click on ‘Apply’. Immediately a pop-up window will appear indicating the number of cells that meet the search criteria.
In this way Kutools is used, being able to see all the cells that meet the requirement by clicking ‘OK’ in the above pop-up window.
How can you count cells less than zero?
Otherwise, cells less than zero can be counted simply by repeating the above procedure. It can be in either of the two ways, with formula or using Kutools.
- The formula is as follows: ‘= COUNTIF ( range; “<0” )’ .
- In Kutools in the ‘Specific type’you will have to select ‘Less than’ or ‘Less than’ .
Either of these two ways is that you can count cells less than zero, considering the way each method works.
Can cells with non-zero values be counted?
On the other hand, we can also count cells with non-zero values as long as we set the search criteria in the correct way.
- With a formula, it will only be enough to establish the search criteria. For example: ‘= COUNTIF ( range; “<> 0” )’ .
- In the case of using Kutools you simply have to put ‘Does not equal’or ‘Not equal to’ in the ‘Specific type’ section .
Other useful functions for accounting
As well as the ‘COUNTIF’ function, there are also a great variety of functions that we can use for our spreadsheets. You will find the entire list organized in alphabetical order on Microsoft support . However, here we talk about some of the most basic.
ADD and SUBTRACT functions
The basic functions ‘ADD ‘ and ‘SUBTRACT’ basically work the same way but with opposite objectives. ‘SUM’ is responsible for adding values established in a certain range, which can be individual values, cell ranges or references. On the other hand, as such there is no ‘SUBTRACT’ function and how is it done?
To sum the syntax is the following: ‘= SUM ( A2: A12 )’ . It can also be: ‘= SUM ( A2: A12, D5: D15 )’ . In both cases we are adding, only that in the first the values from cell A2 to A12 are added while in the second the results of adding the ranges A2 to A12 and D5 to D15 are added.
With this understood, the ‘SUBTRACT’ function basically consists of adding a negative sign (-) to the beginning of any of the numbers, depending on what you want. The simplest way to subtract two numbers is as follows: ‘= 20-10’. By placing this in a cell and pressing ‘Enter’ we will have the result of the subtraction in it.
You can also make use of the ‘SUM’ function using the negative sign so that a negative number is added with a positive one (basic subtraction). This is accomplished with the following syntax: ‘= SUM (100, -25)’ . In the latter case we would be adding 100 plus -25 resulting in 75, which would be the same as subtracting 25 from 100.
PERCENTAGE function
There are several functions that allow us to get percentages or averages. However, percentages can be derived using simple formulas. You simply have to position yourself in the cell where you want to save the percentage and write for example: ‘= A12 / B14’ . This will result in a fractional number that if you go to the toolbar and in the ‘Number’ section you click on ‘Percentage style’ (%) you will see the result in percentage.
There is no doubt that Excel is a spreadsheet tool that has many useful functions. It even offers the possibility of importing and exporting graphs in Excel with different formats to be able to take advantage of its functionalities in other programs .
I hope this complete guide on how to use the ‘COUNTIF’ function has been very useful for you, until next time!
