In the workplace, we often have the need to count data based on specific conditions. For example, counting the number of people in a certain department, or judging whether records that meet the conditions exist in the data table.
At this time, the countif function is simply not easy to use. So, today I will introduce to you the usage of countif function. If you want to get rid of statistics until your eyes are black, let countif count for you.
1. What is the counting function countif?
Count, in English, means statistics, counting. if means if.
Combining the two means that in a specified area, if a certain condition is met, it will be counted. The countif function syntax is as follows:
countif(range,criteria)
This function has only two parameters.
The first parameter range: is the area to be counted and statistics;
The second parameter criteria: is the condition for counting. Can be a number, expression, cell reference, or text string.
Therefore, for the extension of the second parameter, in actual work, countif can meet a variety of counting needs.
2. How to use?
The second parameter is a number
=countif(A:A,10)
Count how many 10’s there are in column A.
The second parameter is the expression
=countif(A:A,">10")
Count how many items are greater than 10 in column A.
The second parameter is the cell reference
=countifA:A,C1)
Count how many items in column A have the same content as C1.
=countif(A:A,">" & amp;C1)
Count how many cells in column A are larger than cell C1.
Pay attention to the way the conditions are written above: If you are comparing with the specified cell value, you need to enclose the operator in English quotation marks, and use text connectors to connect the specified cells.
The second parameter is a text string
= countif(A:A, "王")
Count the number of people in column A whose surname is “Wang” and whose name is three characters. Therefore, Wang Laowu, Wang Xiaoer, etc., as long as their surname is “Wang” and their name is three characters, they will be included in the statistics, while “Wang Ming” will not be included.
(Note: When using wildcards to collect statistics on text data, * represents any multiple characters; ? represents any single character.
3. Practical cases
Through the above introduction, everyone knows the conventional usage of the countif function. Let’s practice it through a case demonstration.
Statistics of recruitment numbers for each department
As shown in the figure below, it is a recruitment information form. There are columns such as the company’s full name, department, education requirements, salary, etc. The following formulas will be used to count the number of people in each department.
The statistical formula in cell I2 is as follows:
=countif($B$2:$B$30,H2)
How do you understand this formula?
The first parameter is cells B2:B30, which is the statistics of the “Department” column.
So what do we count?
The second parameter is the statistical condition. The second parameter is cell H2, which means to count the counts in the specified area based on the content of cell H2. Because the content of cell H2 is “Operations Department”, that is, the “Operations Department” is counted.
Because the formula still needs to be filled in, the counts of other conditions in the specified area must continue to be counted, such as the counts of “Technical Department”, “Product Department”, “Design Department”, etc. In this way, the recruitment numbers of each department are calculated.
After warming up the countif function above, if you were asked to count how many people had a salary higher than 15,000, would you be able to do it instantly?
Because the second parameter of the countif function can use expressions, you only need to use the comparison operator to enter a string expression in this parameter, such as input condition “>15000”. Note that it must be enclosed in English double quotes.
For example, if you want to count the salaries in the salary column that are greater than 15,000, the statistical formula is as follows:
=countif($E$2:$E$30,">15000")
In other words, when judging the salary in the “Salary” column, that is, the “E2:E30” area in the case, the judgment condition is “>15000”. The formula will count the number of salaries in the recruitment list greater than 15,000, and the result is 12.
4. Multi-condition counting function countifs
It is still the same recruitment information table. The following function formula counts the number of recruits with a salary greater than 15,000 in different departments. As shown in the picture:
Enter the statistical formula in cell I3 as follows:
=countifs($B$2:$B$30,H3,$E$2:$E$30,">15000")
How to understand this formula?
The countifs function is used in the formula. The only difference between the countifs function and the countif function is that counif is a single-condition counting, while countifs is a multi-condition counting.
We can use countif to count with a single condition. For example, for the “salary” column, count the number of items greater than 15,000. The formula is:
=countifs($E$2:$E$30,">15000")
We can use the countifs function to count multiple conditions. For example, for the “Department” column and the “Salary” column, we can count how many people in the specified department have a salary greater than 15,000. The formula is:
=countifs($B$2:$B$30,H3,$E$2:$E$30,">15000")
These two conditions are in an “and” relationship, that is to say, the number of people in the “operations department with a salary greater than 15,000” is counted.
alright, do not piffle any more. The same recruitment information table above, if you want to count the number of salaries greater than 15,000 and less than 20,000, how should you write the function formula? Use your own brain to think about it and then look at the answer.
The answer is as follows:
=countifs($E$2:$E$30,">15000",$E$2:$E$30,"<20000")
Of course, because the two counting areas before and after are the same, you can also use the countif function to write here:
=countif($E$2:$E$30,">15000")-countif($E$2:$E$30,">20000")
The number of records with a salary greater than 15,000 must include those with a salary greater than 20,000, so subtracting the two will yield a count greater than 15,000 and less than 20,000.
5. What are the precautions?
Although the countif function is easy to use, you must pay special attention when using it. When countif encounters an ID number, such a bug will appear.
The picture below is an employee identity registration form. We need to check whether the ID number is entered repeatedly.
If you directly perform statistics on the ID number column, the C2 statistical formula is as follows:
=COUNTIF($B$2:$B$11,B2)
If the statistical result is greater than 1, that is, the number of occurrences is greater than 1, it is judged as a duplicate. As shown in the picture above, the results show that many ID cards are duplicated. But manual inspection revealed otherwise.
Let’s take a look at the highlights that are judged to be duplicates:
Why does the system judge the ID cards of “Monkey” and “Sun Dasheng” as duplicates? These two ID numbers are obviously different!
The reason is that although the ID card number in the case demonstration is listed as a text value, in the countif function, both text value and numeric data will be recognized as numerical values for statistics.
In Excel, only 15 significant digits can be retained at most. Values exceeding 15 digits are all reset to 0. So for an 18-digit ID number, the last three digits are all 0 by default. Therefore, the countif function mistakenly recognized the ID numbers of Monkey and Sun Dasheng as the same number.
So how to solve this kind of problem? Wildcards must be used in the second parameter of the countif function to force it to be converted into text for statistics. For example, the modified formula of D2 is:
=countif($B$2:$B$11,B2 & amp;”*”)
It is equivalent to telling the countif function: The content I want to count is the text starting with cell B2. At this time, countif will obediently perform the task.
As shown in the figure below, the specific inspection results show that except for the duplicate ID numbers of Gao Xiaoming and Wang Laowu, the other ID numbers are all accurate.
6. Summary
The above is an introduction to the usage of the countif function. It is easy to understand on paper, so friends must start practicing it after reading it.
I have a little tip for learning functions in excel: Functions that look similar can actually be learned together. For example, here we introduce the countif function. So, can the sumif function and averageif function also be used by analogy?
I am Monkey, a master of the Chinese Academy of Sciences/former IBM senior software engineer/author of “Data Analysis Thinking” with an 8-point score on Douban. I launched a data analysis course on Zhihu Zhixuetang, combining IBM project experience and the front-line business of domestic Internet companies. Cases explain commonly used models + logical frameworks, as well as common data analysis tool training + business practice training. Data analysis skills and thinking walk on two legs, allowing you to become a real data analyst rather than a data processing tool person.
The course lasts for 3 days and 6 hours in total. It starts from the basics and is very friendly to novices with no basic knowledge. It also explains how to do a personal data analysis project. For other job hunting issues, you can also consult professional teachers 1v1. Whether you want to learn a skill to assist in a job, or want to switch to data analysis, you can click on the link at the end of the article to read the original text to learn:
to
Python Machine Learning: 8 Projects for Beginners
Issue
My boss asked me to predict sales for next year, what should I do?
Reply
Understand the relationship between data analysis, machine learning, deep learning, and artificial intelligence with one picture
Gu
Why is Kaggle helpful for finding a job? How to get started?
Click “Read the original text”
Free registration for Data Analysis Training Camp