Counting unique occurances of text in a column

[2182khz]

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)

[Aladin Akyurek]

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)))

[2182khz]

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

[Dave Hawley]

Hi Tom

Have you tried playing with a Pivot Table?

[Mark W.]

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 ]

[Aladin Akyurek]

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.

Aladin

[Mark W.]

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

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.

[Aladin Akyurek]

On 2002-03-22 08:52, Mark W. wrote:

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)))}

Aladin




[ 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 ]

[2182khz]

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*