Use countifs to find out if there is the same data in the other two columns? 5

Use the conditional formatting + countif() function bai to get similar results.

1. Select a1 in zhi (note, this step cannot be omitted, otherwise dao may cause subsequent formula errors);

2. Select column A >> Conditional Formatting

3. Condition 1: Change "value" to "formula" and enter the formula in the box on the right: =countif(b:b,a1)>0" Format > Font > color selection is "Red" > "OK" and exit the conditional format;

Note: The number of a1 cannot be preceded by an absolute reference "$", that is, it cannot be written as $a$1 or a$1).

This method can be applied to WPS** Excel and the steps and formulas have been verified.

Suppose your ** is in the ABCDE column, then enter =if(countif(a:a,d1)>0, different in f1, which is the difference between column A and column D

It should be said that it is okay to find out whether the other two columns are the same data, but in the process of searching, we should pay attention to the correspondence between the parameters of each other.

It's simple, and you can get it quickly with a formula.

You enter the formula below in C2 and drop it down.

countifs(a:a,a2,b:b,b2)=countif(a:a,a2)

If it is shown as ture, then it is all the same, otherwise it is not the same and false

If there are two of them, then there must be the same data, and this is a must.

The cell to which it is referred should be 1 because it is being found.

Look for yes in the other two columns if it's in the same data.

It's certainly not math, the numbers are updated differently every day.

There is the same data in the practice technique, take a look at some news on the Internet, pay more attention to this aspect of the situation, and learn more about it on the Internet.

1. As shown in the figure, we now want to filter out the duplicate data in data A and B. If we use conditional formatting to filter, the message will be confused by the duplicate data in column A, so the data will be inaccurate.

c,b:b)", press the guess silver comic enter key. Here we should pay attention to the order of selecting the number series, because we want to filter out "data a", so we need to give priority to "data b", that is, "c:" in parentheses

c "in front.

<>6. The final filter is the option that "data a" and "data b" are duplicated. Here you will see that the number "2" is displayed because the corresponding row "data b" has two identical data. If there are three identical ones, "3" will be displayed.

1. As shown in the figure, we now want to filter out the duplicate data in data A and B. If we use conditional formatting to filter, the data will be mixed up because of the duplicate data in the column A, so the data will be inaccurate.

c,b:b)". Here we should pay attention to the order of selecting the number series, because we want to filter out "data a", so we need to give priority to "data b", that is, "c:" in parentheses

c "in front.

<>6. The final filter is the option that "data a" and "data b" are duplicated. Here you will see that the number "2" is displayed because the corresponding row "data b" has two identical data. If there are three identical ones, "3" will be displayed.

The countif function can only determine the first 15 digits, and if the first 15 bits are the same, he will think that they are the same, and use sumproduct to solve this problem.

E1 input formula =if(sumproduct(1*(b$2:b$24=c2)),"","No duplicates"Pull down.

=if(countif($b$2:$b$24,c2&"*")=0,"No duplicates","")

For long numbers with more than 15 digits, you must add "*" to force the text and change it to the above formula.

More than 15 people can do this.

if(countif($b$2:$b$24,c2&"*")=0,"No duplicates","")

or =if(countif($b$2:$b$24,c2&"*"),“"No duplicates")

There is nothing wrong with the formula itself.

It should be the data problem. Or maybe there's an error in the workbook that is a circular reference or something that prevents the calculation from being made.

Shows duplicate BC columns:

if(countif(b:b,c2),c2,"") or =if(countif(countif(c:c,b2),b2,"")

Column B is displayed in a duplicate of column C.

if(countfi(c:c,b2),"",b2)""You can add other words or something.