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


The well-known Excel software in the Microsoft Office suite integrates several formulas for numerical calculation and data analysis. Of these, the first to know how to master is to allow counting the number of occurrences of elements present in an array. Futura explains how to use it today.

you will also be interested

Microsoft Excel is software Continued Spreadsheet office automation of Microsoftbetter known as Microsoft Office. Created in 1985, it has been the most popular tool for creating tables containing data and analyzing it for decades. Microsoft Excel integrates multiple functions Numeral Calculationssome basic (addition, subtraction, multiplication, division), others more advanced.

One of these functions is very useful and it is one of the basic commands that must be known: it allows the user to knowevent The elements contained in an array or part of an array. Futura details the steps to use it.

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

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

along with mousenow select the scope where the account is located Door. In our example, we want to know how many times the word “Jon” appears in the first column. Therefore, we select boxes A1 to A15. Note that you can write “A1:A15” directly after the formula.

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

Press “Enter” to validate the command and see the results immediately. We also want to know how many times the name “Jon” appears in the second column. So we have to repeat what we just did. So the formula should be “=COUNTIF(C1:C15;”Jon”)”.

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

Use the “sum” command

In a box, write the command “=SUM(” or select it from the drop-down menu. Then use the mouse to select the range related to the calculation or write it directly into the box, in our example “A16:C16” The last command is “=SUM(A16:C16)” and finally press “Enter” to confirm.

By using the “COUNTIF” command

Same as you did a few minutes ago with the “COUNTIF” command, the problem now is to select all elements of the array by expanding the range using the mouse or writing directly. Ultimately, your command should be something like “=COUNTIF(A1:D15;”Jon”)”. Verify with “Enter” to see the results displayed.

In the case where the elements of the array to be analyzed are numerically related, it is of course possible to know the exact number of occurrences of the value by using the NB:IF function, exactly as we did. seen so far.

But there are other options. For example, we can know the number of times an array element is smaller or larger than a defined value.At this point, replace the content in the quotes with x, where x is the reference value.In our example we want to know how many times number Less than 4 appear in column B of the table, so the command to write is “=NB:SI(B1:B15;)”

Interested in what you just read?

Source link


Please enter your comment!
Please enter your name here