Results 1 to 6 of 6

Thread: Excel 2007 - Count unique formula

  1. #1

    Excel 2007 - Count unique formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2

    Solved - Excel 2007 Count Unique Formula

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

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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. #4

    SOLVED - Excel 2007 - Count unique formula

    Thanks Hercules.

    Chuck

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

  6. #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
  •