UNIQUE().length based on second critera


I am trying to figure out if it’s possible to use the UNIQUE().length formula in combination with other criteria.

I would like to count all the unique values in subtable Column B, but only if they also match X criteria in Column C

As an example, in the attached table, I would want to be able to count the Unique Items in Column A, but only if also sold by Tom. While there are 3 unique items in Column A, only 2 unique items were sold by Tom.

I am sure this is possible but I can’t get any formulas to work!



Hi Terri,

You will need to first create another subtable field, assigned with IF() formula to only return the item value if salesman is Tom, in this case, it would be IF(B1.RAW=“Tom”,A1,""). Then you can apply UNIQUE.length formula to refer to this field instead.
So that you will need to create fields as many salesman you have.

Hi Amy,

Thanks, that works great!