Excel function learning: Let's talk about how to use countif() (detailed case explanation)

In the previous article "Excel function learning: How to quickly count working days, take a look at these two functions! 》, we learned two working day functions. Today we are going to learn countif() and share 5 cases of countif function checking for duplicates. Come and take a look!

There are some very classic function combinations in Excel. The more familiar ones are the INDEX-MATCH combination and the INDEX-SMALL-IF-ROW combination (also called Tiger Balm). combination), of course there are many other combinations. The combination shared today is also very useful. Below, we will use four common questions to let everyone witness the wonderful moments brought by this combination. Of course, we still need to get to know the two people today. Two protagonists: COUNTIF and IF, two functions that everyone is very familiar with.

How to use the COUNTIF function: COUNTIF (range, condition), the function can get the number of times that data that meets the conditions appears in the range. Simply put, this function is used for conditional counting;

Usage of the IF function: IF (condition, result that satisfies the condition, result that does not satisfy the condition), in one sentence, if you give IF a condition (the first parameter), when the condition is established, it will return a result (the second parameter) parameter), when the condition is not met, another result is returned (the third parameter).

Regarding the basic usage of these two functions, I have talked about it many times in previous tutorials, so I won’t go into details here. Let’s take a look at the first problem that occurred after the two of them met: When checking orders The question on

Assume that column A is all the order numbers, and column D is the order number that has been shipped. Now you need to mark the shipped orders in column B (in order to prevent everyone from being dazzled, The arrows only point out two corresponding order numbers):

I think you must be familiar with this question. This question is in the reconciliation I use it often, and maybe some friends can’t wait to shout VLOOKUP. In fact, the formula in column B is like this:

=IF(COUNTIF(D:D,A2)> 0,"shipped","")

First use COUNTIF to make statistics and see that the order number in cell A2 is in D The column appears several times. If it does not appear, it means it has not been shipped. Otherwise, it means it has been shipped.

Therefore, use COUNTIF(D:D,A2)>0 as the condition of IF. If the order appears in column D (the number of occurrences is greater than 0), then "shipped" is returned (note that Chinese characters must be added quotes), otherwise it returns blank (two quotes represent blank).

Now that you understand the first question, let’s look at the second question: COUNTIF to check duplicate cases: How to find duplicate orders

Column A comes from multiple After summarizing the order statistics table registered by the clerk, it is found that some are duplicates (for the convenience of viewing, you can sort the order numbers first). Now you need to mark the duplicate orders in column B:

This is also a problem with a very high ranking rate, and the solution is also very simple. The formula in column B is:

=IF(COUNTIF(A :A,A2)>1,"有","")

##Similar to the previous question, this time it is calculated directly The number of times each order appears in column A, but the condition needs to be changed. It is not greater than 0 but greater than 1. This is also easy to understand. Only orders that appear more than 1 are duplicate orders, so use COUNTIF(A:A ,A2)>1 as the condition, and then let IF return the result we need.

When duplicate orders are found, the third question arises. You need to specify whether to keep the information after the order number. If there are duplicates, keep one:

This problem seems quite troublesome at first glance. In fact, it can be achieved by slightly modifying the formula of question 2: =IF(COUNTIF($ A$2:A2,A2)=1,"Keep","")

Note the COUNTIF here, the range is no longer The entire column is $A$2:A2. This way of writing will change the statistical range as the formula is pulled down. The result is as follows:

It is not difficult to see that the order number with a result of 1 is the first time it appears, which is also the information we need to retain, so when it is used as a condition, equal to 1 is used.

The first three questions are all related to the order number, and the last question is related to Supplier Assessment, which is the key issue that determines whether the contract can be renewed.

According to company regulations, there are six assessment indicators for each supplier. A is the best and E is the worst. If there are two or more E's in the six indicators, the contract will not be renewed. :

The rules are relatively simple. Let’s see if the formula is equally simple:

=IF(COUNTIF (B2:G2,"E")>1,"No","")

This time the range of COUNTIF changes OK, count the number of occurrences of "E" in the range B2:G2. Also pay attention to adding quotation marks. When the statistical result is greater than 1, it means that the supplier has more than two negative reviews (if you insist Use greater than or equal to 2, I have no objection), and then use IF to get the final result.

The last question I want to talk about must be familiar to partners in financial positions. Sometimes we will encounter this situation: There is a positive and a negative situation in a column of data. At this time, it is necessary to change the future The offset data is marked (extracted) , such as the example in the picture:

This problem may have caused headaches for many people. In fact, using Today's combination of these two functions is easy to solve. The formula is:

=IF(COUNTIF(A:A,-A2)=0,A2,"")

Pay attention to the condition -A2 in COUNTIF here, that is, find the number that can cancel each other out with A2. If not, get A2 through IF, and vice versa. Null value, using a negative sign cleverly solves a troublesome problem.

Through the above five cases, you may have a feeling that the combination of these two functions is relatively easier to understand than some other function combinations. As long as you find the right idea, this combination can be used for many problems. to handle. This is also true. If you are good at using COUNTIF to count various conditions, and then use the IF function to get more diverse results, ifcountif is often used to filter duplicate data. Solving problems does not necessarily require difficult functions. It is also very pleasant to use simple functions well.

Related learning recommendations: excel tutorial

The above is the detailed content of Excel function learning: Let's talk about how to use countif() (detailed case explanation). For more information, please follow other related articles on the PHP Chinese website!