How to use the COUNTIF Function to find Duplicates and Unique Values In Excel

We can also use the COUNTIF function for finding duplicates in one column, between two columns, or in a row.

Example 1: Find and count duplicates in 1 column

The formula =COUNTIF(A2:A10, A2)>1 will spot all duplicate entries in the range A2:A10 while another function =COUNTIF(B2:B10, TRUE) will tell you how many duplicates are there

Example 2: Count duplicates between two columns

Suppose you have two separate lists, say lists of names in columns B and C, and you want to find out how many appear in both columns, you can use Excel COUNTIF in combination with the SUMPRODUCT function to count duplicates.

=SUMPRODUCT0((COUNTIF(B2:B1000,C2:C1000)>0)*(C2:C1000<>””))

To count how many unique names are there in Column C, i.e. names that do NOT appear in Column B:

=SUMPRODUCT((COUNTIF(A2:A1000,C2:C1000)=0)*(C2:C1000<>””))

Example 3: Count duplicates and unique values in a row

You can use one of the below formulae to count duplicates or unique values in a certain row rather than a column.

Count duplicates in a row:

=SUMPRODUCT ((COUNTIF(A5:J5,A5:J5)>1)*(A5:J5<>” “))

Count unique values in a row:

=SUMPRODUCT((COUNTIF(A5:J5,A5:J5)=1*(A5:J5<>” “))

Important Points to remember

• COUNTIF is not case-sensitive.
• COUNTIF will only check one condition at a time.
• COUNTIF needs a range for the evaluation.
• In COUNTIF the criteria must be enclosed in the double-quotes.
• If the criteria are evaluated from a different cell then that cell must not be enclosed in the double-quotes. E.g. “=” & E5
• It gives an error if the matching string exceeds 255 characters.

FAQs

What is the COUNTIF function in Excel?

The COUNTIF function in Excel is used to count the number of cells that match a single condition applied. It can include Dates, Numbers, and Texts. It uses various logical operators like <(Less Than), >(Greater Than), >=(Greater Than or Equal to), <=(Less Than or Equal to), =(Equals to), and <>(NOT) for matching the condition. It also uses some wildcards like * and ? for partial matching.

When to use an ampersand in a COUNTIF formula?

If you use a number or a cell reference in the exact match criteria, you need neither ampersand nor quotes. For example

=COUNTIF(A2:A10,100) Or

=COUNTIF(A2:A10,D2)

If your criteria is an expression with a cell reference or another Excel function, you have to use quotes(“”) to start the text string and ampersand (&) to concatenate and finish the string off For example :

=COUNTIF(A2:A10,”>”&D2) Or

=COUNTIF(A2:A10,”<=”&Today())

How can we use COUNTIF in Excel on a non-contiguous range or a selection of cells?

Excel doesn’t work on non- adjacent range, nor does its syntax allow specifying several individual cells as the first parameter.

You can use a combination of several COUNTIF functions:

=COUNTIF(A2,”>0″) + COUNTIF(B3,”>)”) + COUNTIF (C4,”>0″)

The COUNTIF function in Excel is used to count the number of cells that match a single condition applied. It can include Dates, Numbers, and Texts. It uses various logical operators like <(Less Than), >(Greater Than), >=(Greater Than or Equal to), <=(Less Than or Equal to), =(Equals to), and <>(NOT) for matching the condition. It also uses some wildcards like * and ? for partial matching.