

Note that the COUNTIFS function was introduced in Excel 2007, so the method we're going to look at in this lesson will only work if you have Excel 2007, Excel 2010 or Excel 2013.
Ms excel find duplicates how to#
COUNTIF allows you to use just one criteria, whereas COUNTIFS allows you to use multiple criteria. You can find out more about COUNTIF in our lesson on how to Use COUNTIF to count the cells in a range that match certain values. It is closely related to the COUNTIF function.

The COUNTIFS syntaxĬOUNTIFS is a function that allows you to count only those rows in a spreadsheet where certain criteria are met. For example, a Desktop Monitor has been ordered twice, as have Backup Tapes for the Server. A sample of this data can be seen in the following picture:Īs you can see in this example, there are several examples where the Product and Part Ordered are repeated. We need to identify how often each spare part is being ordered for each machine. Our data table lists orders of spare parts for several computers. This lesson uses an example of a product order table.

Excel offers a number of ways to find rows that contain duplicate values. However, most of them focus on finding rows where the value in just one column is duplicated. In this lesson, we look at how to use the COUNTIFS function to find rows with duplicate entries in two or more columns. We then use the COUNTIFS function in combination with Excel's Conditional Formatting feature to highlight duplicate and triplicate rows.
