UNIQUE().length based on second critera


#1

Hello!

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!
image

Thanks,

Terri


#2

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.


#3

Hi Amy,

Thanks, that works great!

Terri