How to know the number of occurrences of an element in Excel?

Microsoft Excel is the software continuation spreadsheet office automation of Microsoftbetter known as Microsoft Office. Created in 1985, it has been the most popular tool for decades when it comes to creating a table containing data and analyzing it. Microsoft Excel integrates multiple functions of numerical calculationsome basic (addition, subtraction, multiplication, division), others more advanced.

One of these functions is very practical, it is one of the essential commands to know: it allows the user to know the number ofoccurrences of an element contained in the array or part of the array. Futura details the steps to follow to use it.

First, open the Excel file containing the data you want to analyze. In our example, the table has 15 rows and four columns. Of the four columns, two contain names and two contain numeric values.

Then click on the box in which you want to see the result of your function. Then enter the command “=COUNTIF(“. You can write it out completely or select “COUNTIF” from the drop-down menu that appears when you start typing the command.

With the mousenow select the range on which the account door. In our example, we want to know the number of times the word “Jon” appears in the first column. We therefore choose boxes A1 to A15. Note that you can write “A1:A15” directly after the formula.

Add a “; in the formula. Then, you must enter the name of the element you want to count between quotes. In our example, you must therefore write “Jon”. Close the parentheses to complete the command. There should be no free space between them.

Press the “Enter” key to validate the command and see the result displayed immediately. We also want to know the number of times the name “Jon” appears in the second column. We must therefore repeat the operation we have just performed. The formula should therefore be “=COUNTIF(C1:C15;“Jon”)”.

Now, if you want to know the total number of occurrences of the element “Jon”, there are two possibilities.

Using the “SUM” command

In a box, write the command “=SUM(” or select it from the drop-down menu. Then choose the range on which the calculation relates with the mouse or write it directly in the box, in our example “A16:C16” The final command is “=SUM(A16:C16)” Finally press “Enter” to validate.

By using the “COUNTIF” command

In the same way as you proceeded when you used the “COUNTIF” command a few minutes earlier, it will now be a question of selecting all the elements of the array by widening the range either with the mouse or by writing it directly. Ultimately, your command should look like “=COUNTIF(A1:D15;”Jon”)”. Validate with “Enter” to see the result displayed.

In the case where the elements of the array that you want to analyze relate to numerical values, it is of course possible to know the number of times that a precise value appears by using the NB:IF function, exactly in the same way what we have seen so far.

But there are also additional options. We can for example know the number of times an array element is less than or greater than a defined value. At that point, replace what’s in quotes with x, x being the reference value. In our example, we want to know how many times numbers less than 4 appear in column B of the table, the command to be written is therefore “=NB:IF(B1:B15;)”