Thread: Counting Unique Items in Range (Excel 2003)

1. Counting Unique Items in Range (Excel 2003)

Hello to all,

I need to count unique names in a list, but only when the unique name meets a condition (a specific title, for example) in another column. So let's say I have my list of names (many repeats) in B19:B224, and my list of titles in D19224. What I need to do is to count the unique names in the B column where the title in the D column is "Director", or "Vice President", etc.

I can count the unique names in the B column with this formula:
=SUM(IF(FREQUENCY(MATCH(\$B\$19:\$B\$224,\$B\$19:\$B\$224, 0),MATCH(\$B\$19:\$B\$224,\$B\$19:\$B\$224,0))>0,1))

I have thus far been unable to figure out a formula that will allow me to count the unique names in the B column based on specific titles in the D column.

Thanks in advance for any and all help...

2. Re: Counting Unique Items in Range (Excel 2003)

Try the following formula. It must be entered as an array formula, i.e. it must be confirmed with Ctrl+Shft+Enter instead of just Enter.

=SUM(IF(FREQUENCY(IF(D19224="Director",MATCH(B19:B224,B19:B224,0),""),IF(D 19224="Director",MATCH(B19:B224,B19:B224,0),""))>0,1 ))

You can put the title to compare to in a cell instead of directly in the formula. Say that F1 contains Director, or whatever title you want to count unique items for.

=SUM(IF(FREQUENCY(IF(D19224=F1,MATCH(B19:B224,B19:B224,0),""),IF(D19224=F1,MATCH(B19:B224,B19:B224,0),""))>0,1))

3. Re: Counting Unique Items in Range (Excel 2003)

Hans, you are a life saver (as always). The formula works perfectly.

Now, please don't shoot me, but I have a second, related question. Is it possible to amend this formula to add another condition? For example, this formula returns a count of the unique names in column B based on Title in column D. Suppose I need to further break down the count by seeing how many unique names that are, for example, Managing Directors in North America or Europe or Japan (column H)?

I know a pivot table will do all this, but for the purposes of this model a formula is much more efficient. We are currently using pivot tables to get this info, but they need to be rebuilt every week and the various filters need to be applied manually, all of which is time consuming. A formula that does it all would be so much better.

Thanks very much for the formula you provided. That alone will be a big help. It would be great if there were a formula to add more conditions.

Regards,

4. Re: Counting Unique Items in Range (Excel 2003)

Using the following method, you can extend this to any number of conditions:

=SUM(IF(FREQUENCY(IF((D19224="Director")*(H19:H224="Japan"),MATCH(B19:B224, B19:B224,0),""),IF((D19224="Director")*(H19:H224="Japan"),MATCH(B19:B224, B19:B224,0),""))>0,1))

or with Director in cell P1 and Japan in cell P2:

=SUM(IF(FREQUENCY(IF((D19224=P1)*(H19:H224=P2),MATCH(B19:B224,B19:B224,0)," "),IF((D19224=P1)*(H19:H224=P2),MATCH(B19:B224,B19:B224,0)," "))>0,1))

5. Re: Counting Unique Items in Range (Excel 2003)

Hans, you prove yourself a genius once again. The formula works perfectly. I just couldn't get the construction. I was trying to put the third condition outside the FREQUENCY and it didn't work. It didn't occur to me to construct the formula with the extra condition(s) inside the FREQUENCY.

Thank you again. This is terrific.

Regards,

Posting Permissions

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