Count number of cells containing the same text or value in row
Results 1 to 5 of 5

Thread: Count number of cells containing the same text or value in row

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Count number of cells containing the same text or value in row

    Hi,
    Can anyone write me a formula that counts cells which have exactly the same value or the same text by each row?

    The result should be looking something like this:

    A B C Result
    AlicK* Alick* Alick* 2
    B.Mus. B.MUS. B.MUS. 2
    Cl. Cl cl. 0
    Corel Corel Corel 3

    Any help would be appreciated.


    Thanks.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,764
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Count number of cells containing the same text or value in row

    Try this

     ABCD
    1   Result
    2AlicK*Alick*Alick*2
    3B.Mus.B.MUS.B.MUS.2
    4Cl.Clcl.1
    5CorelCorelCorel3

    CellArray Formula
    D2{=MAX(SUM(IF(EXACT(A2:C2,A2),1)),SUM(IF(EXACT(B2:C2,B2),1)))}


    It should be 1 instead of 0, because at least one data exists.

    But if you want 0. try this.

    {=IF(MAX(SUM(IF(EXACT(A2:C2,A2),1)),SUM(IF(EXACT(B2:C2,B2),1)))=1,0,MAX(SUM(IF(EXACT(A2:C2,A2),1)),SUM(IF(EXACT(B2:C2,B2 ),1))))}


    Array formulas
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Regards Dante Amor

  3. #3
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count number of cells containing the same text or value in row

    Here's a Power Query version. It has the same issue with the 1 instead of a 0. Seems like 1 should be the answer logically.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        MaxCount = Table.AddColumn(Source, "MAX", each List.Max(Table.Column(Table.Group(Table.FromList(Record.FieldValues(_)), {"Column1"}, {{"Count", each Table.RowCount(_), type number}}),"Count")))
    in
        MaxCount
    But, this code will replace the 1s with 0s.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        MaxCount = Table.AddColumn(Source, "MAX", each List.Max(Table.Column(Table.Group(Table.FromList(Record.FieldValues(_)), {"Column1"}, {{"Count", each Table.RowCount(_), type number}}),"Count"))),
        Replace = Table.ReplaceValue(MaxCount,1,0,Replacer.ReplaceValue,{"MAX"})
    in
        Replace
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count number of cells containing the same text or value in row

    Thank you DanteAmor. I have used your first formula and worked perfect.

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,764
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Count number of cells containing the same text or value in row

    Quote Originally Posted by gregaExcel View Post
    Thank you DanteAmor. I have used your first formula and worked perfect.
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

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
  •