exceljet.net Open in urlscan Pro
2600:3c00::f03c:91ff:fee7:ea6e  Public Scan

Form analysis
2 forms found in the DOM

GET /search

<form action="/search" method="get" id="views-exposed-form-search-page" accept-charset="UTF-8">
  <div>
    <div class="views-exposed-form">
      <div class="views-exposed-widgets clearfix">
        <div id="edit-query-wrapper" class="views-exposed-widget views-widget-filter-search_api_views_fulltext">
          <div class="views-widget">
            <div class="form-item form-type-textfield form-item-query" role="search">
              <input class="auto_submit form-text form-autocomplete" data-search-api-autocomplete-search="search_api_views_search" placeholder="Search..." type="text" id="edit-query" name="query" value="" size="30" maxlength="128" autocomplete="OFF"
                aria-autocomplete="list" role="combobox"><input type="hidden" id="edit-query-autocomplete" value="https://exceljet.net/index.php?q=search_api_autocomplete/search_api_views_search/-" disabled="disabled"
                class="autocomplete autocomplete-processed">
              <span class="element-invisible" aria-live="assertive" aria-atomic="true" id="edit-query-autocomplete-aria-live"></span>
            </div>
          </div>
        </div>
        <div class="views-exposed-widget views-submit-button">
          <input type="submit" id="edit-submit-search" value="Search" class="form-submit">
        </div>
      </div>
    </div>
  </div>
</form>

POST https://sendy.exceljet.net/subscribe

<form id="sendy_function_pdf" class="antibot" action="https://sendy.exceljet.net/subscribe" method="POST" accept-charset="utf-8">
  <label for="email" hidden="">Email</label>
  <input type="email" name="email" placeholder="email address" id="email" class="text-input" required="">
  <div style="display:none;">
    <label for="hp">HP</label>
    <input type="text" name="hp" id="hp">
  </div>
  <input type="hidden" name="SignupLocation" id="SignupLocation" value="function pdf">
  <input type="hidden" name="list" value="T1JX0z264YewQdKf7gXIig">
  <input type="hidden" name="subform" value="yes">
  <input type="submit" name="submit" id="submit" value="Get the PDF">
</form>

Text Content

Skip to main content
 * Login
 * Cart


EXCELJET

QUICK, CLEAN, AND TO THE POINT

 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Blog





RANGE CONTAINS DUPLICATES

Generic formula 

=OR(COUNTIF(range,range)>1)

Summary 

To test if a range or list contains duplicates, you can use a formula based on
the COUNTIF function and the OR function. In the example shown, the formula in
cell E5 is:

=OR(COUNTIF(data,data)>1)

where data is the named range B5:B16.

Note: this is an array formula and must be entered with control + shift + enter,
except in Excel 365 and Excel 2021.

Powered By

10





Share

Next
Stay




FILTER function basic example

NOW
PLAYING

Conditional formatting formula in a table

NOW
PLAYING

How to rank with a pivot table

NOW
PLAYING

3 Basic Array Formulas

NOW
PLAYING

FILTER data between two dates

NOW
PLAYING

How to see arrays in formulas

NOW
PLAYING

Spilling and the spill range

NOW
PLAYING

What is an array formula

NOW
PLAYING

Arrow Left #1 Icon Created with Sketch.
Arrow right #1 Icon Created with Sketch.
Explanation 

In this example, the goal is to test if a given range contains duplicate values
and return TRUE if duplicates exist and FALSE if not. This is essentially a
counting problem and the solution is based on the COUNTIF function, which counts
values in a range that meet supplied criteria. The formula used in E5 is:

=OR(COUNTIF(data,data)>1)

where data is the named range B5:B16.


BACKGROUND STUDY

Below are related links to help you understand how this formula works:

 * What is an array formula? - 3 min video
 * What is an array? - 3 min video


COUNTIF FUNCTION

Working from the inside-out, the core of the formula is based on the COUNTIF
function:

COUNTIF(data,data)

Here, data (B5:B16) is given for both range and criteria. Typically, criteria is
supplied as a single value, but in this case data contains 12 values. The result
is that COUNTIF returns 12 counts (one for each value) in a single array like
this:

{1;1;2;1;1;1;1;2;1;1;1;1}

In "modern" versions of Excel that support dynamic arrays, you can enter the
COUNTIF formula above as a standalone formula and you will see the results spill
onto the worksheet. Most values in the array are 1 but notice that the third
value and eighth value are 2, which indicate duplicate values. The value 155
occurs twice at these positions in the range, which is why the count for that
number is 2.

In this particular problem, we don't care about the specific numbers returned by
COUNTIF, we only care if any number is greater than 1. Therefore, we use the
greater than operator (>) to check the result:

=COUNTIF(data,data)>1
={1;1;2;1;1;1;1;2;1;1;1;1}>1

The result is an array of TRUE and FALSE values.

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}

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.


OR FUNCTION

The array above is returned directly to the OR function:

=OR({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})

The OR function returns TRUE if any given argument evaluates to TRUE, and
returns FALSE only if all supplied arguments evaluate to FALSE. 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.


SUMPRODUCT ALTERNATIVE

To avoid an array formula that requires Control + Shift + Enter, you can use the
SUMPRODUCT alternative below:

=SUMPRODUCT(--(COUNTIF(data,data)>1))>0

As above, the counts are checked for any numbers greater than 1, resulting in an
array of TRUE and FALSE values. The double negative (--) converts the TRUE and
FALSE values to 1s and 0s, and the result is delivered to SUMPRODUCT, which
returns the total. Finally, the total from SUMPRODUCT is checked against zero.
Since the total is greater than zero, the formula returns TRUE as a final
result.

This is still an array formula but the SUMPRODUCT function handles the array
operation natively, so it is not necessary to use Control + Shift + Enter.


COUNT DUPLICATES

To count the number of duplicates in the range you can adapt the formula like
this:

=SUMPRODUCT(--(COUNTIF(data,data)>1))

Note: this is also an array formula, but because SUMPRODUCT function can handle
the array operation natively, it is not necessary to use control + shift +
enter.

The configuration of COUNTIF is the same as the original formula above, so we
end up with the same array of TRUE and FALSE values. The double negative (--)
converts the TRUE and FALSE values to 1s and 0s, and the result is delivered to
SUMPRODUCT, which returns the sum of the array:

=SUMPRODUCT({0;0;1;0;0;0;0;1;0;0;0;0}) // returns 2

Note: the SUM function will also work fine in place of SUMPRODUCT, but the
formula be entered with control + shift + enter in older versions of Excel.

Attachments 
range contains duplicates.xlsx
Author 
Dave Bruns

Related formulas 
Highlight duplicate values
COUNTIF simply counts the number of times each value appears in the range. When
the count is more than 1, the formula returns TRUE and triggers the rule. When
you use a formula to apply conditional formatting, the formula is evaluated
relative to...
Highlight duplicate rows
In the formula, COUNTIFS counts the number of times each value in a cell appears
in its "parent" column. By definition, each value must appear at least once, so
when the count > 1, the value must be a duplicate. The references are carefully
locked...
Flag first duplicate in a list
At the core, this formula is composed of two sets of the COUNTIF function
wrapped in the IF function . The outer IF + COUNTIF first checks to see if the
the value in question (B4) appears more than once in the list: = IF ( COUNTIF (
$B$4:$B$11 , B4...
Find duplicate values in two columns
This formula uses two named ranges, "range1" (B5:B12) and "range2" (D5:D10). The
core of this formula is the COUNTIF function, which returns a count of each
value in both range inside the AND function: COUNTIF ( range1 , B5 ) // count in
range1...
Sort and extract unique values
Note: the core idea of this formula is adapted from an example in Mike Girvin's
excellent book Control+Shift+Enter . The example shown uses several formulas,
which are described below. At a high level, the MMULT function is used to
compute a numeric...
Count unique text values in a range
This formula is more complicated than a similar formula that uses FREQUENCY to
count unique numeric values because FREQUENCY doesn't work with non-numeric
values. As a result, a large part of the formula simply transforms the
non-numeric data into...
Range contains specific text
The COUNTIF function counts cells that meet supplied criteria, and returns a
count of occurrences found. If no cells meet criteria, COUNTIF returns zero. The
asterisk (*) is a wildcard for one or more characters. By concatenating
asterisks before...
Related functions 
Excel OR Function

The Excel OR function returns TRUE if any given argument evaluates to TRUE, and
returns FALSE if all supplied arguments evaluate to FALSE. For example, to test
A1 for either "x" or "y", use =OR(A1="x",A1="y"). The OR function...

Excel COUNTIF Function

COUNTIF is an Excel function to count cells in a range that meet a single
condition. COUNTIF can be used to count cells that contain dates, numbers, and
text. The criteria used in COUNTIF supports logical...

Excel SUMPRODUCT Function

The Excel SUMPRODUCT function multiplies ranges or arrays together and returns
the sum of products. This sounds boring, but SUMPRODUCT is an incredibly
versatile function that can be...

Good links 
Check list for duplicate numbers (Chandoo)
Related courses 
Core Formula
Dynamic Array Formulas


EXCEL FORMULA TRAINING

Formulas are the key to getting things done in Excel. In this accelerated
training, you'll learn how to use formulas to manipulate text, work with dates
and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with
criteria, dynamically rank values, and create dynamic ranges. You'll also learn
how to troubleshoot, trace errors, and fix problems. Instant access. See
details here.

500 Formulas | 101 Functions


DOWNLOAD 100+ IMPORTANT EXCEL FUNCTIONS

Get over 100 Excel Functions you should know in one handy PDF.

Email
HP


TOPICS

 * Formula Basics
 * Formula Examples
 * Conditional Formatting
 * Pivot Tables
 * Excel Tables
 * Excel Charts
 * Shortcuts
 * More...


KEY FUNCTIONS

 * IF function
 * VLOOKUP function
 * XLOOKUP function
 * FILTER function
 * SUMIFS function
 * COUNTIFS function
 * SUMPRODUCT function
 * INDEX and MATCH
 * More functions...

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help
you work faster in Excel. We create short videos, and clear examples of
formulas, functions, pivot tables, conditional formatting, and charts. Read
more.




Your site is so resourceful, simple, yet very elegant. I was searching the web
for a simple excel formula to convert a conventional construction dimension, ex.
44'-10 5/8" to just inches for a cheat sheet I was creating for my crew. At
first, the top ranked searches were useless but as I scrolled down the list,
yours stood out. Once I landed on your site, I was instantly excited. I had to
stop myself from diving into the rabbit hole of future potentials and just focus
on my original task. -Andy


EXCEL VIDEO TRAINING

Quick, clean, and to the point.

Learn more

© 2012-2022 Exceljet. Terms of use
 * Home
 * About
 * Blog
 * Contact
 * Feedback

 * Twitter