# Thread: Count number of cells containing the same text or value in row Thanks:  1 Post #5327864 (1) Likes:  1 Post #5327864 (1)

1. ## 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. ## Re: Count number of cells containing the same text or value in row

Try this

 A B C D 1 Result 2 AlicK* Alick* Alick* 2 3 B.Mus. B.MUS. B.MUS. 2 4 Cl. Cl cl. 1 5 Corel Corel Corel 3

 Cell Array 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

3. ## 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```

4. ## 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. ## Re: Count number of cells containing the same text or value in row

Originally Posted by gregaExcel
Thank you DanteAmor. I have used your first formula and worked perfect.