Search code examples
excelexcel-formulaexcel-2007

Count unique values IF certain text is found

I want to count the unique values against each name. The image below will explain what I need. Here you can see that Second appears 5 times in the name column but it contains only 2 unique values. I have tried the frequency method but it counts all the unique values without considering the name. Any help or suggestions?

enter image description here

Solution

  • I can't see your row numbers from that screenshot, so I'll presume that the headers for your source table are in row 3.

    In F5, array formula**:

    =COUNT(1/FREQUENCY(IF(B$4:B$16=E5,C$4:C$16),C$4:C$16))

    and copied down.

    To incorporate Defined Names for your two source ranges, go to Name Manager (Formulas tab) and create three new Defined Names:

    Name: LRow Refers to: =MATCH(REPT("z",255),$B:$B)

    Name: Names Refers to: =$B$4:INDEX($B:$B,LRow)

    Name: Values Refers to: =$C$4:INDEX($C:$C,LRow)

    The array formula then becomes:

    =COUNT(1/FREQUENCY(IF(Names=E5,Values),Values))

    Regards

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).