+ Reply to Thread
Results 1 to 7 of 7

Count unique value for each row in a number of Columns

  1. #1
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Count unique value for each row in a number of Columns

    Hi,

    Pls. help. I'm having problem counting unique person in a number of Columns for each row. I only know how to count unique value if it is in 1 column.

    Activities JOINT1 JOINT2 JOINT3 JOINT4
    WELDING 1 Allan Allan
    WELDING 2 Allan Bob
    WELDING 3 Bob Allan Bob
    WELDING 4 David Carl
    WELDING 5 Francis Francis Francis Francis
    WELDING 6 Carl Ed Carl Carl
    WELDING 7 Francis Francis Ed


    NAME # of Welding (Formula in this column)

    Allan 3
    Bob 2
    Carl 2
    David 1
    Ed 2
    Francis 2



    Sorry, I wanted to upload the file but I don't understand why I can't upload this time. I copied the example in this post from excel file and the values are not align properly.

    Thanks
    Ricky
    Last edited by rickyilas; 05-23-2012 at 08:48 PM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Count unique value for each row in a number of Columns

    hi rickyilas, so how many times is Allan supposed to count? 3 times?
    =COUNTIF(B:C,B1)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Count unique value for each row in a number of Columns

    u might also need a unique list of values. u can do it by:
    1) copying Joint1 to another place & use "Remove duplicates"
    2) using Advanced Filter, selecting Unique Values
    3) using formulas http://www.get-digital-help.com/2009...om-one-column/

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Count unique value for each row in a number of Columns

    Hi Ricky,

    Assume the names started from B2 to across & down.

    A11 to down has all names, so try in B11, then copy down.

    =SUMPRODUCT(SIGN(COUNTIF(OFFSET(B$1:IV$1,ROW(A$1:A$10)-ROW(A$1),),A11)))

    Or,

    =SUMPRODUCT(SIGN(MMULT((B$2:IV$10=A11)*ROW(B$2:B$10),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS($B$1:$IV$1))))))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Re: Count unique value for each row in a number of Columns

    Yes Benishiryo, Allan is appearing 3 times in each welding activities. Your formula is counting 4 which sums up allan as 2 in Welding 1. It should only count Allan as 1 in welding 1. tnx.

  6. #6
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Re: Count unique value for each row in a number of Columns

    Haseeb, Both your formula works fine! Thank you so much...

  7. #7
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Count unique value for each row in a number of Columns

    With your data in A1:E8
    and
    I1: a name...Allan

    This ARRAY FORMULA, completed by pressing CTRL+SHIFT+ENTER (instead of just ENTER) returns the number of rows containing that name
    J1: =COUNT(1/FREQUENCY(IF(($B$2:$E$10=I1),ROW($A$2:$A$10)),ROW($A$2:$A$10)))

    or...this regular (but more difficult to understand) formula returns the same count
    J1: =COUNT(1/MMULT((B$2:E$10=I1)*ROW(B$2:B$10),{1;2;3;4}))

    Copy either formula down as many rows as you have name listed.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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