# Excel: Advanced Filter with complex criteria

**URL:** <https://boards.straightdope.com/t/excel-advanced-filter-with-complex-criteria/609523>\
**Category:** Factual Questions\
**Created:** [January 14, 2012, 6:53pm UTC](https://boards.straightdope.com/t/excel-advanced-filter-with-complex-criteria/609523 "2012-01-14T18:53:03Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Don\_t\_Call\_Me\_Shirley](https://avatars.discourse-cdn.com/v4/letter/d/f4b2a3/32.png) [@Don\_t\_Call\_Me\_Shirley](https://boards.straightdope.com/u/Don_t_Call_Me_Shirley)\
**Post date:** [January 14, 2012, 6:53pm UTC](https://boards.straightdope.com/t/excel-advanced-filter-with-complex-criteria/609523/1 "2012-01-14T18:53:03Z")

</div>

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:

```auto

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?

---

<div class="post-metadata">

**Author:** ![keno](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@keno](https://boards.straightdope.com/u/keno)\
**Post date:** [January 16, 2012, 5:27pm UTC](https://boards.straightdope.com/t/excel-advanced-filter-with-complex-criteria/609523/2 "2012-01-16T17:27:02Z")

</div>

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.