Excel: Advanced Filter with complex criteria

I use advanced filters in Excel a lot. Until now I really thought I knew what I was doing. But now I am trying to pull a list of numbers that are duplicated on one sheet onto another sheet.

Here is an example: on one sheet I have a list of products and product numbers. I want to pull a list of product numbers that are duplicated onto another sheet. On the sheet called “products,” column a is called “productname” and I only want to include duplicate product numbers of widgets. The product numbers are in column H. I set up the criteria range like so:



productname
widget                 =countif(products!$H:$H,H2)>1

The extract range is simply “productnumber” to match column H on the products sheet.

I’ve googled this and checked at least a dozen sites, which all agree that I am doing this right. But it is not working. What am I doing wrong?

If I understand what you’re doing, I don’t think Advanced Filter criteria will work the way you’re trying to use it.

If you copy a modified version of your criteria formula =countif(products!$H:$H,H2) alongside your database in a column labeled say “DUPE”, you can then apply an advanced criteria DUPE > 1, you should get your desired results.