Hi gebobs,
The formula used in the attached file is:
=SUM(IF(FREQUENCY(IF($A$2:$A$27&$C$2:$C$27=A20&C20 ,MATCH($B$2:$B$27,$B$2:$B$27,0)),IF($A$2:$A$27&$C$ 2:$C$27=A20&C20,MATCH($B$2:$B$27,$B$2:$B$27,0)))>0 ,1))
It’s an array formula, needs to be confirmed with CSE (Ctrl+Shift+Enter, not just Enter after editing the formula)
The key is the IF condition that sends the Type Column to Frequency function, but only the rows that contains Flange&Chocolate, Flange&Vanilla,Widget&Chocolate in joined columns A and C.
There is a typo error in Log sheet, Type column in cell B21, Yellow value has a trailing space, that is wrongly identifying this row as unique. I recommend using data validation lists, to avoid errors like this.
Cheers,
Catalin
|