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

[gregaExcel]

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:

[TABLE="width: 376"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]AlicK*[/TD]
[TD]Alick*[/TD]
[TD]Alick*[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B.Mus.[/TD]
[TD]B.MUS.[/TD]
[TD]B.MUS.[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Cl.[/TD]
[TD]Cl[/TD]
[TD]cl.[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Corel[/TD]
[TD]Corel[/TD]
[TD]Corel[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated.


Thanks.

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td >Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >AlicK*</td><td >Alick*</td><td >Alick*</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >B.Mus.</td><td >B.MUS.</td><td >B.MUS.</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Cl.</td><td >Cl</td><td >cl.</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Corel</td><td >Corel</td><td >Corel</td><td style="text-align:right; ">3</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >D2</td><td >{=MAX(SUM(IF(EXACT(A2:C2,A2),1)),SUM(IF(EXACT(B2:C2,B2),1)))}</td></tr></table></td></tr></table>

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))))} <tbody> </tbody>

<tbody>
</tbody>

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

 

[lrobbo314]

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.

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.

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

 

[gregaExcel]

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

 

[DanteAmor]

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

I'm glad to help you. Thanks for the feedback.