The formula used in E5 is:
wheredatais thenamed rangeB5:B16.
Typically, criteria is supplied as asinglevalue, but in this casedatacontains 12 values.
This is the information we need to solve the problem.
If any value in the array is TRUE, it means we have duplicates.
If all values are FALSE, it means there are no duplicates.
To check the array, we can use the OR function.
The final result is TRUE, since at least one value in the array is TRUE.
Note: COUNTIF will automatically ignore empty cells in this configuration, which return a count of 0.
Finally, the total from SUMPRODUCT is checked against zero.
Since the total is greater than zero, the formula returns TRUE as a final result.
OR returns TRUEif any condition is TRUE.
If all conditions are FALSE, the OR function returns FALSE.
