The table can have either all or some matching matching values in a row. Row 1 has all values matching and row 2 has number 10 as matching

Example data

Cell A	Cell B	Cell C	Cell D
35	35	35	35
24	49	10	10

The table can have either all or some matching matching values in a row. Row 1 has all values matching and row 2 has number 10 as matching

Example data

Cell A	Cell B	Cell C	Cell D
35	35	35	35
24	49	10	10

Expecting something like

Cell A	Cell B	Cell C	Cell D	Duplicate Count
35	35	35	35	4
24	49	10	10	2

I have tried

=COUNTIF(Cell A1: Cell D1, Cell A1) - 1 

which only works if all 4 values are same.

• google-sheets

Share Follow edited Mar 10 at 11:15 Srikanth Reddy asked Mar 10 at 10:52 Srikanth ReddySrikanth Reddy 3711 silver badge11 bronze badge 3

• 4 How would you count a row that contains 10-10-35-35? – Excellor Commented Mar 10 at 10:58
• Also, are you using google-sheets or Excel? Pick one, they are very similar but not the same. – Excellor Commented Mar 10 at 10:59
• There's also the question of whether the duplicates have to be consecutive or not. It's further compounded by the fact that 'in a row' can mean either 'in the same row of the spreadsheet' or 'consecutive' plus the fact that all the examples are in fact showing consecutive duplicates. – Tom Sharpe Commented Mar 10 at 17:48

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 1

You can use COUNTIF, REDUCE and TEXTJOIN

The comment that what happens if there are multiple duplicates are very insightful and I do really think that it must be taken care off, with this reasons I created a solution that can provide you either a single number or a string where duplicated is joined by ",". I designed the formula so you can edit the final output with ease.

Sample Formula

=TEXTJOIN(",", TRUE, LET(x, A1:D1, REDUCE("",UNIQUE(TOCOL(x)), LAMBDA(a,c, IF(COUNTIF(x,c)>1,HSTACK(a,COUNTIF(x,c)),)))))

Sample Result

A	B	C	D	Result
11	11	35	35	2,2
22	10	10	10	3
45	45	45	45	4

References:

Textjoin - Google Sheets

Reduce - Google Sheets

Use filter() and countif(), like this:

=counta(ifna(filter(A1:D1, 1 < countif(A1:D1, A1:D1))))

See filter() and countif().

The formula is in E16 (dragdown)

Show the count of identical values separated with comma.

=TEXTJOIN(", ",TRUE,IF(FREQUENCY(A16:D16,UNIQUE(A16:D16))>1,FREQUENCY(A16:D16,UNIQUE(A16:D16)),""))

Here's a single formula that works using BYROW.

=BYROW(A2:D4,LAMBDA(r,TEXTJOIN(",",1,MAP(UNIQUE(TOCOL(r)),LAMBDA(_,IF(COUNTIF(r,_)>1,COUNTIF(r,_),))))))