+ Reply to Thread
Results 1 to 9 of 9

count if - number of types of cells

  1. #1
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    count if - number of types of cells

    hi,

    does anyone know how to count number of types of cells in a column.

    for example, below column has 3 types of cells:

    xxx
    yyy
    zzz
    xxx
    xxx
    zzz
    yyy

    many thanks!!
    Last edited by afgi; 07-05-2011 at 12:33 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: count if - number of types of cells

    Hi afgi,

    I'd be looking at delete duplicates for an answer. If you were to copy that column to a blank spot and delete duplicates on the copied column, it would reduce it to unique values. You could easily count how many were left.

    Is that what you wanted?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: count if - number of types of cells

    This formula will count only different non-blank entries in A2:A100

    =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

    change range as required
    Audere est facere

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: count if - number of types of cells

    @ DLL,

    Can you explain in words how this formula works?

  5. #5
    Registered User
    Join Date
    07-04-2011
    Location
    UK, Buckingham
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: count if - number of types of cells

    Hi,

    I'm using "countif"

    i trust you'll have a summary section that will have

    XXX =
    YYY=

    etc.... if you do then insert the =COUNTIF(H16:H498,"YYY") or XXX or ......

  6. #6
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: count if - number of types of cells

    hi,

    dear MarvinP , I cannot remove data, because I need it. I need to find a way to get this result in pivot table.
    So, I have a question to daddylonglegs. Are you able to put your formula in excel and attache it as a file in this thread?

    many thanks

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: count if - number of types of cells

    Quote Originally Posted by MarvinP View Post
    @ DLL,

    Can you explain in words how this formula works?
    Yes......

    ...assume that the sample data that afgi provided is in A2:A8, i.e.

    xxx
    yyy
    zzz
    xxx
    xxx
    zzz
    yyy

    Then this basic formula

    =COUNTIF(A2:A8,A2:A8)

    returns an array like this

    ={3;2;2;3;3;2;2}

    Note that there is a 3 in every xxx position because there are 3 of those, and a 2 in every yyy or zzz position because there are 2 each of those.

    Now because there will always be three 3s (or four 4s....or five 5s, or 6 sixes) then if you divide 1 by that array and sum the results you get the count of different values, i.e. if I use this

    =1/COUNTIF(A2:A8,A2:A8)

    that gives me

    ={1/3;1/2;1/2;1/3;1/3;1/2;1/2}

    so


    =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))=3 as required

    The extra parts are to avoid errors. If A8 is blank then the COUNTIF part returns

    ={3;1;2;3;3;2;0}

    and when you divide 1 by that array the zero leads to a #DIV/0! error.......so it turns out that making the sceond argument into text avoids that, i.e.

    =COUNTIF(A2:A8,A2:A8&"")

    gives you

    {3;1;2;3;3;2;1}

    ...so that avoids #DIV/0! error but now

    =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8&""))=4

    because the blank is being counted......so to get 3 you can add another array to the SUMPRODUCT to filter out blanks, i.e.

    =SUMPRODUCT((A2:A8<>"")+0,1/COUNTIF(A2:A8,A2:A8&""))

    which now gives 3 as expected

    ....and you can shorten that to this

    =SUMPRODUCT((A2:A8<>"")/COUNTIF(A2:A8,A2:A8&""))

    see attached
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: count if - number of types of cells

    Dear daddylonglegs, works great. MANY THANKS)

  9. #9
    Registered User
    Join Date
    08-26-2015
    Location
    Bandung, Indonesia
    MS-Off Ver
    2011
    Posts
    1

    Re: count if - number of types of cells

    Dear daddylonglegs, thank you for providing the formula, it really helpful

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1