Thanks:  0
Likes:  0

# Thread: Counting unique occurances of text in a column

1. Hello,

We know that to count unique occurances of numbers (part numbers in this case) in a column we can say:
=SUM(IF(FREQUENCY(A21:A341, A21:A341)>0,1,0))

But what can we say when we want to know how many unique occurances of a text string (part names) there are in a similar range? (b21:b341)

2. On 2002-03-22 06:44, 2182khz wrote:
Hello,

We know that to count unique occurances of numbers (part numbers in this case) in a column we can say:
=SUM(IF(FREQUENCY(A21:A341, A21:A341)>0,1,0))

But what can we say when we want to know how many unique occurances of a text string (part names) there are in a similar range? (b21:b341)
The formula you posted can be extend by using MATCH to obtain the desired count. But, the following will work for both:

=IF(LEN(B21:B341),SUMPRODUCT(1/COUNTIF(B21:B341,B21:B341)))

3. Hello,
Interesting approach yielded undesired result. Attached a little sample to clarify.

???
=IF(LEN(IR21:IR341),SUMPRODUCT(1/COUNTIF(IR21:IR341,IR21:IR341))) returns null #VALUE
???

-this yields 8
=SUM(IF(FREQUENCY(a10:a20, a10:a20)>0,1,0))
-part types ?# should yield 3
=

PARTNUMBERS PART TYPES
8 ?# types

101 NUT
102 BOLT
103 SCREW
104 NUT
105 BOLT
106 SCREW
106 SCREW
107 BOLT
108 SCREW
106 SCREW
106 SCREW

4. Hi Tom

Have you tried playing with a Pivot Table?

5. On 2002-03-22 07:35, 2182khz wrote:
Hello,
Interesting approach yielded undesired result. Attached a little sample to clarify.

???
=IF(LEN(IR21:IR341),SUMPRODUCT(1/COUNTIF(IR21:IR341,IR21:IR341))) returns null #VALUE
???

-this yields 8
=SUM(IF(FREQUENCY(a10:a20, a10:a20)>0,1,0))
-part types ?# should yield 3
=

PARTNUMBERS PART TYPES
8 ?# types

101 NUT
102 BOLT
103 SCREW
104 NUT
105 BOLT
106 SCREW
106 SCREW
107 BOLT
108 SCREW
106 SCREW
106 SCREW
=SUM(IF(FREQUENCY(A10:A20, A10:A20),1)) produces 8 because the are 8 unique PARTNUMBERS. This is a correct result!

{=IF(LEN(B10:B20),SUMPRODUCT(1/COUNTIF(B10:B20,B10:B20)))}

Should be entered as an array formula.

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

When it's entered properly it returns the correct results -- 3.

BTW, I'd stick with a formula approach. PivotTables themselves don't support a unique count.

[ This Message was edited by: Mark W. on 2002-03-22 08:23 ]

6. On 2002-03-22 07:35, 2182khz wrote:
Hello,
Interesting approach yielded undesired result. Attached a little sample to clarify.

???
=IF(LEN(IR21:IR341),SUMPRODUCT(1/COUNTIF(IR21:IR341,IR21:IR341))) returns null #VALUE
???

-this yields 8
=SUM(IF(FREQUENCY(a10:a20, a10:a20)>0,1,0))
-part types ?# should yield 3
=

PARTNUMBERS PART TYPES
8 ?# types

101 NUT
102 BOLT
103 SCREW
104 NUT
105 BOLT
106 SCREW
106 SCREW
107 BOLT
108 SCREW
106 SCREW
106 SCREW
I get a count of 8 for the first column of your data and 3 for the second (not array-entered and need not to be), with the formula I proposed.

7. I get a count of 8 for the first column of your data and 3 for the second (not array-entered and need not to be), with the formula I proposed.

Aladin, if =IF(LEN(B10:B20),SUMPRODUCT(1/COUNTIF(B10:B20,B10:B20))) isn't entered as an array formula it returns #VALUE!. I believe array formula entry is necessary because the LEN worksheet function expects a text value and not an array.

8. On 2002-03-22 08:52, Mark W. wrote:
I get a count of 8 for the first column of your data and 3 for the second (not array-entered and need not to be), with the formula I proposed.

Aladin, if =IF(LEN(B10:B20),SUMPRODUCT(1/COUNTIF(B10:B20,B10:B20))) isn't entered as an array formula it returns #VALUE!. I believe array formula entry is necessary because the LEN worksheet function expects a text value and not an array.
Mark, the real trouble is that it will not work the way I intended: the existence of blanks in the range, leading #DIV/0!

I propose to switch to the array-formula:

{=SUM(IF(LEN(B21:B341),1/COUNTIF(B21:B341,B21:B341)))}

[ This Message was edited by: Aladin Akyurek on 2002-03-22 09:05 ]

[ This Message was edited by: Aladin Akyurek on 2002-03-22 09:06 ]

9. Thank you sirs for you fine and mind expanding help!
The last solution from Aladin indeed works!

*breaks out the manual to readup on arrays*

## User Tag List

#### Posting Permissions

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