CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formula
Leonid Royzman
Leonid Royzman

Reputation: 11

Add 95 to a cell if the name and date in that row match another row with the same name and date

I am using this formula :

=IF(COUNTIFS(A:A, A1, B:B, B1) > 1, + 95, )

To parse through two columns, column A has names and Column B has dates, if a duplicate row exists anywhere else in the columns add 95. I seem to be mistaken as this returns wrong data.

Column A Column B Column C
Roy 1/1/25 95
Bob 1/1/25
Roy 1/1/25 95
Bob 1/2/25

Should look like above

Upvotes: 0

Views: 64

Answers (1)

Babanana
Babanana

Reputation: 1476

Finding Duplicate of Combined Values of 2 Columns

Based on your Data I figured out that if there is more than 1 duplicate of your number then the formula fails, and also if the data is unsorted. I really can't say the issue for that however, I have tried a different approach to work around the behavior I have noticed. I have taken advantage of Filter and Byrow for this.

Sample Formula

=LET(z, MAP(A2:A,B2:B, LAMBDA(x,y, IF(x <>"",JOIN("/",x,y),""))), BYROW(z, LAMBDA(r, IF(r<>"",IF(COUNTA(FILTER(z, z=r))>1,95,""),""))))

Please adjust the range on the formula accordingly. Make sure that 2 ranges are the same size

Reference:

Byrow

Map

Filter

Sample Output

Column A Column B This Formula Your Formula
Roy 1/1/25 95 95
Bob 1/1/25
Roy 1/1/25 95 95
Bob 1/2/25
mark 1/3/25 95 95
mark 1/3/25 95 95
mark 1/3/25 95
Roy 1/1/25 95
mark

Upvotes: 0

Related Questions