Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Counting unique occurances of text in a column

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Detroit
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #3
    New Member
    Join Date
    Mar 2002
    Location
    Detroit
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom

    Have you tried playing with a Pivot Table?



  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    Mar 2002
    Location
    Detroit
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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*

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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