Conditional formatting duplicates with two conditions

Question

We have the following data

	Col. B	Col. C
3	noun	spring
4	ver.	spring
5	ver.	spring

We need rows 4 and 5 (both columns B and C) to turn red because they are duplicates

We have tried

=(COUNTIF(B$3:B377,B3)>1)*(COUNTIF(C$3:C377,C3)>1)

=AND((COUNTIF(B$3:B377,B3)>1),(COUNTIF(C$3:C377,C3)>1))

=COUNTIF(B$3:B377&C$3:C377,B3&C3)>1

but cannot make it work.

Using =COUNTIFS(B$3:B377,B3,C$3:C377,C3)>1 works for just column B

I'm not completely sure about it, but spreadsheet formulas might actually go to https://superuser.com/ instead of here — Guilherme Taffarel Bergamin, Oct 25 '22 at 17:52
@GuilhermeTaffarelBergamin No they don't. Have [a look](https://stackoverflow.com/questions/tagged/google-sheets-formula) — Swen, Oct 25 '22 at 17:54

doubleunary · Answer 1 · 2022-10-26T13:47:51.690

0

Use the correct mix of absolute and relative references in your conditional formatting custom formula rule:

=countifs($B$3:$B$377, $B3, $C$3:$C$377, $C3) > 1

The question does not specify whether columns B and C should be considered tuples or if columns B and C should be considered separately. To handle the latter case, use countif(), like this:

=(countif($B$3:$B$377, $B3) > 1) * (countif($C$3:$C$377, $C3) > 1)

edited Oct 26 '22 at 13:47

answered Oct 25 '22 at 18:17

doubleunary

13,842
3
18
51

Sorry. It does not work. – Swen Oct 25 '22 at 23:00
Hmm... I think the question is a bit ambiguous because it does not explicitly specify whether B and C should be considered together or separately. Edited the answer to add a solution for the "together" case. – doubleunary Oct 26 '22 at 13:49
Maybe I did not phrase it correctly. Sorry. – Swen Oct 26 '22 at 14:38

Giselle Valladares · Answer 2 · 2022-10-26T14:23:06.267

0

I use a countif like this, this is only taking consideration column B:

=countif($B$2:$B$377, $B2)>1

And it look like this:

To make it work with all the columns, I simply added the columns as individual ranges. Like this:

Update:

For both columns, you can use:

=AND(countif($B$2:$B$377, $B2)>1,countif($C$2:$C$377, $C2)>1)

edited Oct 26 '22 at 14:23

answered Oct 25 '22 at 18:46

Giselle Valladares

2,075
1
4
13

Maybe right. BUT. Not what we asked. We need two conditions. – Swen Oct 25 '22 at 23:02
Sorry, you are right. I didn't take in consideration the the second condition. I updated the answer too. – Giselle Valladares Oct 26 '22 at 14:21

score 0 · Answer 3 · answered Oct 25 '22 at 22:57

0

Sorry but none of suggested answers or other links worked.

But finally found correct solution.

=COUNTIFS(B$3:$B$377,$B3,C$3:$C$377,$C3)>1

This is the only solution.

answered Oct 25 '22 at 22:57

Swen

577
2
10

That is not using the correct mix of absolute and relative references. – doubleunary Oct 26 '22 at 07:11

Conditional formatting duplicates with two conditions

3 Answers3