Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Unique with an added IF (Excel 2000)

    I am using a formula I found here for counting unique values:

    =SUM(IF(COUNTIF(A1:A100,A1:A100)=0, "", 1/COUNTIF(A1:A100,A1:A100)))

    Works great!! EXCEPT, I need to add a condition. If the corresponding cell in column B is empty, don't count that cell.

    For example, if I had:

    [pre]A B
    1 AA
    2
    3 BB

    I want the formula = 2.

    Thanks!!
    Troy

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Counting Unique with an added IF (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Troy

    I am not sure if you can do this via a worksheet function. But I have said these words before only to be proven wrong by the talent and creativity of the members in these lounges.

    Essentially you want to figure out the row of the cell where you have ab empty cell in the range B1:B100 or the range you are working with, and then tell the worksheet function to not count that cell, something like (A1:Ax-1)AX+1:A100)

    I think a VBA User function will do the trick, if you want to go that way.

    Lets wait and see what members come up with.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Counting Unique with an added IF (Excel 2000)

    Hi Troy,

    Try:
    =SUM(IF(COUNTIF(A1:A100,A1:A100)*COUNTIF(B1:B100,B 1:B100)=0, "", 1/COUNTIF(A1:A100,A1:A100)))

    I think this works provided there is always a value in ColA when there is a value in ColB. For example,
    A B
    1 X
    2 ..
    3 Z
    works, but
    A B
    1 X
    .. Y
    3 Z
    gives the wrong result.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Unique with an added IF (Excel 2000)

    Array-enter:

    =COUNTDIFF(IF(B1:B3<>"",A1:A3,""))-1

    COUNTDIFF is part of the morefunc.xll add-in which you can download from:

    http://longre.free.fr/english/index.html

    By the way, the array-formula you are currently using can be shorter:

    =SUM(IF(LEN(A1:A100),1/COUNTIF(A1:A100,A1:A100)))

    =COUNTDIFF(A1:A100)

    which is normally entered, does the same if you don't have any formula-generated "" in A1:A100.

    Aladin
    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Unique with an added IF (Excel 2000)

    Sorry I'm so late replying to this. I appreciate the suggestion, but this formula did not work for me. Instead, I got a fraction (.84??).

    The AddIn suggested in the next post worked fine.

    Thanks!!
    Troy

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Unique with an added IF (Excel 2000)

    Thanks!! That worked beautifully!
    Troy

Posting Permissions

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