Search code examples
excelrangeuniquecountifsumproduct

Countif with 2 ranges, 1 range of data can be variable but contain duplications

I'm trying to perform a count based on this data table, column 3 is the column i'm trying to automate. What i want it to do is count how many unique products each person made. I'm not sure if i'm overcomplicating but every solution i try doesn't work - i played around with sumproduct but couldn't get it to work.

The problem is the item made by column contains a static list of names, but in theory the product could contain anything.

What formula would work in the "unique products made by person" column please?

Any help appreciated.

Data

Solution

  • Alex,

    Here's the formula:

    =IFERROR(ROWS(UNIQUE(FILTER($E$2:$E$10,$D$2:$D$10=$A2))),0)

    Note: Refers to the setup below. enter image description here

    HTH