# 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.  Reply With Quote

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  Reply With Quote

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```  Reply With Quote

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.   Reply With Quote

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.   Reply With Quote

## User Tag List

#### Tags for this Thread

b.mus, corel, count values, number of cell in row, row #### Posting Permissions

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