Counting Unique values

[archimaitreya]

Hi,

Can anyone please help me with a formula to count the unique values. In the attached excel, I want to have the unique value under each category. The excel has the expected results as well.

I want values "1" in Cell C2. This represents that for ABC client and under A&M sector, only 1 unique sub sector was engaged. However, for cell D2, the expected value is "1" as for ABC and under HC sector, only 1 sub sector has been engaged.

Please help!!!

[tim201110]

=SUM(--(FREQUENCY(IF((Data!$D$2:$D$14=$B2)*(Data!$E$2:$E$14=C$1),MATCH(Data!$F$2:$F$14,Data!$F$2:$F$14,)),ROW(Data!$E$2:$E$14)-2)>0)) as array formula

[archimaitreya]

=SUM(--(FREQUENCY(IF((Data!$D$2:$D$14=$B2)*(Data!$E$2:$E$14=C$1),MATCH(Data!$F$2:$F$14,Data!$F$2:$F$14,)),ROW(Data!$E$2:$E$14)-2)>0)) as array formula

Thank you so much.. this works perfectly well.