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

[pre]A B
1 AA
2
3 BB

I want the formula = 2.

Thanks!!
Troy

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

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

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

Array-enter:

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

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.

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