Counting unique occurances of text in a column

2182khz

New Member
Joined
Mar 21, 2002
Messages
3
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)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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)))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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

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
 
Upvote 0
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*
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top