Google Sheets Conditional Formatting – Highlighting Entire Row Using COUNTIFS

conditional formattingformulasgoogle sheets

For the range C2:Z100 we apply the following formula to find duplicate students' enrollments using conditional formatting:

=COUNTIFS($C:$C,C2 , $D:$D,D2)>1

(where column C is the last name and D is the first name e.g. John Smith)

Unfortunately, we only get single duplicate cells in column C highlighted.

But… We want to have the entire row highlighted.

We have tried different variations and suggestions from the site as here and here or here (as well as many others). Nothing seems to work for the entire row. Also tried with COUNTIF , =AND (COUNTIFS($C:$C,C2 , $D:$D, D2)>1), COUNTA.No luck.

On the other hand this formula =$B2="G" highlights an entire row

What do we do wrong?

EDIT:

This is a link to a demo sheet as requested

Best Answer

Conditional formatting is based on the single reference which is then multiplied on a given range. To assure reference is single you need to lock it down with $ symbol. In this particular case, you need to lock columns because conditions are static column-wise and keep them dynamic row-wise. Therefore:

US syntax: =COUNTIFS($C:$C, $C2, $D:$D, $D2)>1
EU syntax: =COUNTIFS($C:$C; $C2; $D:$D; $D2)>1

Related Solutions

Google Sheets – How to Conditionally Format the Minimum Value in a Row

Sooo close (I think!). Please try:

=D3=min($D3:$L3)

=min(D3:L3) returns a value but CF needs a TRUE to trigger. So we check "the current cell" (D3) to see whether its value matches that of the minimum in the range D3:L3. If it does we trigger the formatting.

I say "the current cell" because quite which that is depends on the Range: selection. Here it is the same as that from which the minimum value is selected (ie D3:L3). CF has a kind of rastering feature, equivalent to checking whether to trigger the first cell in the Range: (and if TRUE, doing so) then automatically stepping on the E3, F3 cell (and if TRUE, doing so) etc until reaching the end of Range:.

(I have no idea what the actual sequence is but since all done with one click don't see that matters much.)

Without anchors (the $s in $D3:$L3) the minimum range also would auto adjust during the process, so D3 would be tested against D3:L3 but E3 against E3:M3, and so on. (This particular question, where the MIN range and Range: are the same, may not however be the most suitable one for explaining the anchors!)

Google-sheets – How to check if a cell is blank for Conditional formatting

With the range set to: B3:O1000

Try:

=AND(ISBLANK(B3),INT($A3)=INT(today()))

or even shorter:

=AND(ISBLANK(B3),$A3=today())

Best Answer

Related Solutions

Google Sheets – How to Conditionally Format the Minimum Value in a Row

Google-sheets – How to check if a cell is blank for Conditional formatting

Related Topic