Author: Oscar Cronquist Article last updated on November 28, 2018

Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any employee is free to take a plethora of different courses and they could take them all on the same day if they want. What I am trying to do is see if any employee has taken any course more than once in any given day?

Conditional formatting formula:

=SUM(COUNTIF(\$A2, \$A\$2:\$A\$35)*COUNTIF(\$B2, \$B\$2:\$B\$35)*COUNTIF(\$C2, \$C\$2:\$C\$35))>1

### Explaining Conditional formatting formula

#### Step 1 - Count record

The next COUNTIF function counts values based on a condition or criteria.

COUNTIF(\$A2, \$A\$2:\$A\$35)*COUNTIF(\$B2, \$B\$2:\$B\$35)*COUNTIF(\$C2, \$C\$2:\$C\$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*COUNTIF(\$B2, \$B\$2:\$B\$35)*COUNTIF(\$C2, \$C\$2:\$C\$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*COUNTIF(\$C2, \$C\$2:\$C\$35)

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*{1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 1; 1; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0}

#### Step 2 - Multiply arrays

All three arrays must return 1 in order to indicate a row as duplicate, to accomplish this we need to use AND logic. AND logic is performed using the asterisk charcater between arrays, in other words, arrays are multiplied.

COUNTIF(\$A2, \$A\$2:\$A\$35)*COUNTIF(\$B2, \$B\$2:\$B\$35)*COUNTIF(\$C2, \$C\$2:\$C\$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*{1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 1; 1; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0}

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

#### Step 3 - Sum numbers in array

The SUM function adds numbers in a cell range or array and returns a total.

SUM(COUNTIF(\$A2, \$A\$2:\$A\$35)*COUNTIF(\$B2, \$B\$2:\$B\$35)*COUNTIF(\$C2, \$C\$2:\$C\$35))

becomes

SUM({1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})

and returns 1.

#### Step 4 - Check if record is a duplicate

If total is larger than 1 then we know it is a duplicate, the larger than character allows us to compare the total to a given condition, the returned value is a boolean value, TRUE or FALSE.

SUM(COUNTIF(\$A2, \$A\$2:\$A\$35)*COUNTIF(\$B2, \$B\$2:\$B\$35)*COUNTIF(\$C2, \$C\$2:\$C\$35))>1

becomes

1>1 and returns FALSE. Cell A2 is not highlighted.