# How to write an IF-formula with multiple OR/AND arguments to detect duplicates?

Update2: Changing all commas (,) to semicolons (;) gives me "Could not find a matrix value."

Update1: The formula that Rubén suggests:

``````=IF(COUNTIF(\$A\$2:A&\$B\$2:B&\$C\$2:\$C,A2&B2&C2)+COUNTIF(\$A\$2:A&\$D\$2:D&\$E\$2:D,A2&D2&E2)>0,1,0)
``````

Gets me this error. Any suggestions?

I have a Google sheet with 100 rows (individuals) and the following columns:

``````NAME (A) BIRTHDAY (B) BIRTHPLACE (C) TIME OF DEATH (D) PLACE OF DEATH (E)
``````

I would like to create a formula in a new column (F) that outputs the value 1 if a person has the same NAME, BIRTHDAY and BIRTHPLACE or NAME, TIME OF DEATH and PLACE OF DEATH. Otherwise the output value should be 0.

Is this possible and if yes, how?

Assuming that the first row is used for column headers, add the following formula to F2

```=ArrayFormula(
IF(
OR(
COUNTIF(\$A\$2:A&\$B\$2:B&\$C\$2:C,A2&B2&C2)>1,
COUNTIF(\$A\$2:A&\$D\$2:D&\$E\$2:E,A2&D2&E2)>1
)
,1
,0
)
)
```

Then fill down the above formula as necessary

• Thank you Rubén!! Will try it as soon as I'm at a computer again. One question: will this formula check all rows in my document (no matter how many names i have, marking the ones that matches any with the value 1 in column F? I new to this but from what I can see the formula only points to row 2? Sorry if it's a dumb question. – user1151640 Mar 16 '16 at 12:19
• Sorry, I forgot to include `\$` in the initial cell of the range references to make them fix references. I corrected the formula. References without `\$` are "auto-adjustable" when you fill down or fill right the adjacent cells with them. You should try with a simple formulas like `=A2` and `=\$A\$2` to see how this works . – Rubén Mar 16 '16 at 12:36
• Thanks! But the formula is not working, it seems to be selecting the correct columns/ranges but gives me the standard error message "errors in formula". Any suggestions? – user1151640 Mar 16 '16 at 18:15
• Sorry again. I updated the answer. – Rubén Mar 16 '16 at 19:11
• Thanks again for you patience and kindness! Unfortunately it's still not working: "Formula error", se updated status – user1151640 Mar 16 '16 at 23:09