# Thread: Count unique entries? (XL97, WinNT4)

1. ## Count unique entries? (XL97, WinNT4)

Does anyone know if there is a way of counting the *unique* entries in a column? Sort of the opposite of using a filter ...

I have a number of spreadsheets giving company names and authorised signatories for that company, which means each company name appears as many times as there are individuals who can sign on its behalf, and I need to know how many companies are there.

Any help would be greatly appreciated!

2. ## Re: Count unique entries? (XL97, WinNT4)

Say your company names are in A1:A100.

The following formula, entered as an array formula (confirm with Ctrl+Shift+Enter), will return the number of unique company names in this range:

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

3. ## Re: Count unique entries? (XL97, WinNT4)

Wow, talk about quick service! Many thanks, Hans, I'll try that.

I did think it might be something to do with one of the 'count' functions (although I was looking under 'subtotal') but I wasn't able to track it down!

<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

4. ## Re: Count unique entries? (XL97, WinNT4)

<img src=/S/sad.gif border=0 alt=sad width=15 height=15> I must be doing something wrong, Hans, 'cos it didn't work! I got the '#DIV/0!' error. I do just replace both instances of A1:A100 with the actual range, don't I?

5. ## Re: Count unique entries? (XL97, WinNT4)

Beryl,

It has to be entered as an Array formula. After you type the formula in the cell press Crtl and Shift while pressing enter key.
Have a Great Holiday Season everyone!
And, yes you replace the A1:A100,A1:A100 with the actual range.

Chuck

6. ## Re: Count unique entries? (XL97, WinNT4)

Yes, replace the example ranges with your ranges but it is *vital* to enter it as an array fomula (as Hans pointed out).

That means type the formula then press ctrl shift enter and not just enter. If you get this right then the cell will display your formula inside curly brackets.

(not) stuck

7. ## Re: Count unique entries? (XL97, WinNT4)

If there are empty values in your range, the 1/COUNTIF(...) will result in division by 0.

For ranges with empty values, use this slightly more complicated formula:

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

entered as an array formula (Ctrl+Shift+Enter) and with all instances of A1:A100 replaced by your range.

8. ## Re: Count unique entries? (XL97, WinNT4)

<img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> Sorry, Hans - it would help if I actually read it properly! <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

Works perfectly once I use Ctrl-Shift-Enter!

Just out of curiosity, though - what exactly is it doing, because I can't work out WHY this works, although it unquestionably does!

Many thanks <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

9. ## Re: Count unique entries? (XL97, WinNT4)

Better:

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

again entered using control+shift+enter.

10. ## Re: Count unique entries? (XL97, WinNT4)

Hi Hans,

I don't think your solution for ranges with empty cells:
=SUM(IF(COUNTIF(A1:A100,A1:A100)=0,0,1/COUNTIF(A1:A100,A1:A100)))
works correctly. If I put:
=MOD(ROW(),10)
in cell A1 and copy this down to row 10, your formula returns 100, even though there are only 10 non-zero values. Copying it down to row 20, returns 50, whilst copying it down to row 30, returns 33.3333, and so on. In each case, there's only 10 unique values. Aladin's formula:
=SUM(IF(LEN(A1:A100),1/COUNTIF(A1:A100,A1:A100)))
does seem to work correctly, though.

Cheers

PS: This probably invalidates my solution for Troy Wells (see <post#=207221>post 207221</post#>) too, since his formula and my modification of it were based on a similar approach to yours. Ho hum.

11. ## Re: Count unique entries? (XL97, WinNT4)

Hi Bery,

I'm glad it works now. The link in my first reply explains about exotic uses of array formulas. I've tried to give a short explanation (not very successful, I fear) of this particular formula in <post#=200801>post 200801</post#>.

12. ## Re: Count unique entries? (XL97, WinNT4)

Hi Macropod,

My formula returns incorrect results if the range contains zero values (whether as a constant or as the result of a formula). This shouldn't be a problem for Beryl (who started this thread) since she is counting company names. But in general, Aladin Akyurek's formula is to be preferred - it is short, and more important, it's correct.

13. ## Re: Count unique entries? (XL97, WinNT4)

Hi Hans, thanks for the comments - however, by the time Aladin posted his solution, I had already used yours in some three dozen or so spreadsheets, so since, as you commented, the empty values are not a problem for me, I think I'll stick with what I've got!

Merry Christmas and many thanks for all your help!

<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

#### Posting Permissions

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