Excel 2007 - Count unique formula

[chamdan]

Hi,

I need the formula to count the number of unique resources.

Column A Column L
Category Resource name
BA-3 AAA
BA-3 ABC
BA-3 AAA
BA-3 BBB
BA-3 ABC
BA-3 BCD
BA-3 BBB
BA-3 ABC
What would be the formula to count the unique Resources for which the Category is BA-3

Regards,

Chuck

[chamdan]

{=sum(1/countif(l5:l14,l5:l14))}

Regards,

[Hercules1946]

Hello
Assuming that your category and Resource Names as listed start in A2 and B2:
Enter this formula in a spare cell. When youve typed it in dont press ENTER, press CTRL+SHIFT+ENTER instead.

=SUM(1/COUNTIF($B$2:$B$9,$B$2:$B$9&""))

It will then look like this in the formula bar:

{=SUM(1/COUNTIF($B$2:$B$9,$B$2:$B$9&""))} and (hopefully) the answer 4.

HTH
Hercules

[chamdan]

Thanks Hercules.

Chuck

[Kevin@Radstock]

Non array:
=SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9&""))

[chamdan]

Thanks!