# Thread: Excel 2007 - Count unique formula

1. ## Excel 2007 - Count unique formula

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

2. ## Solved - Excel 2007 Count Unique Formula

HTML Code:
`{=sum(1/countif(l5:l14,l5:l14))}`
Regards,

3. 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

4. ## SOLVED - Excel 2007 - Count unique formula

Thanks Hercules.

Chuck

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

6. ## SOLVED - Excel 2007 - Count unique formula

Thanks!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•