Cleanup formula page
Duplicate Rows by Two Columns Formula
Use this when one column is not enough to define a duplicate, such as email plus region.
Find duplicate rows by two columns.
The two maya@example.com and East rows are flagged, while the row with a blank email stays blank.
Copy formulas
=IF(OR(A2="", B2=""), "", IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", ""))=IF(OR(A2="", B2=""), "", IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", ""))Excel and Google Sheets use the same COUNTIFS pattern for two-column duplicate keys. Standard COUNTIFS matching is not case-sensitive in either app.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
The two maya@example.com and East rows are flagged, while the row with a blank email stays blank.
How the formula works
- OR checks whether either key cell is blank before running the duplicate test.
- COUNTIFS checks the Email and Region columns across the same row set.
- A combination is flagged only when the same pair appears more than once.
- COUNTIFS is not case-sensitive; use the EXACT version below when letter case makes two keys different.
| Syntax piece | Role in the formula |
|---|---|
| OR(A2="",B2="") | Leaves the result blank when either part of the combined key is missing. |
| $A$2:$A$100, A2 | Counts rows with the same Email as the current row. |
| $B$2:$B$100, B2 | Restricts the count to rows that also have the same Region. |
| >1 | Labels the row only when the complete Email and Region pair appears more than once. |
Verified examples
=IF(OR(A2="", B2=""), "", IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", ""))Excel: The sample contains two maya@example.com rows in East and one row with a blank Email. Returns: Both Maya/East rows are flagged; the blank-email row remains blank
=IF(OR(A2="", B2=""), "", A2&"|"&B2)Google Sheets: Use the first sample row with maya@example.com in A2 and East in B2. Returns: maya@example.com|East
=IF(OR(A2="", B2=""), "", IF(SUMPRODUCT(--EXACT($A$2:$A$100, A2), --EXACT($B$2:$B$100, B2))>1, "Duplicate row", ""))Excel: Use this variation only when letter case must distinguish two keys. Returns: Only pairs with identical text and letter case are flagged
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| Blank rows are labeled as duplicates | The formula was copied without the OR blank guard. | Keep the initial IF(OR(A2="",B2=""),"",...) wrapper. |
| Visually identical pairs are not flagged | One key contains hidden spaces or a nonprinting character. | Normalize both key columns before running COUNTIFS, or use cleaned helper columns. |
| Names with different letter case are treated as duplicates | COUNTIFS is not case-sensitive. | Use the page's EXACT plus SUMPRODUCT variation when letter case is part of the key. |
When not to use this formula
- Use a single-column duplicate check when one field uniquely identifies a record, and fix recurring duplicate creation in the source system when possible.
Alternatives
| Alternative | When to use it |
|---|---|
| Visible helper key | Join Email and Region with a delimiter when reviewers need to inspect and filter the combined key directly. |
| Duplicate Checker Formula Builder | Use when one column is sufficient and configurable labels or blank handling are needed. |
Related formulas
Official references
- COUNTIFS function from Microsoft
FAQ
Why are both copies of a duplicate pair flagged?
COUNTIFS checks the full range and returns a count greater than one for every row that belongs to the repeated pair.
How do I flag only the second and later matching pairs?
Use expanding ranges that end on the current row instead of fixed full ranges, so the first occurrence has a count of one.