/ How to

How To Count and Sum Duplicate Values in Google Sheets Spreadsheets

Working with large datasets in Google Sheets often leads to duplicate values. These duplicates can make analysis more difficult and lead to inaccurate insights. Fortunately, Google Sheets provides several easy methods to identify, count, and sum duplicate values.

Finding Duplicates in Google Sheets

The first step is identifying duplicate values in your dataset. Here are three simple methods:

Using Conditional Formatting

  1. Highlight the column or data range you want to check for duplicates
  2. Click Format > Conditional Formatting
  3. Under “Format cells if…” select Custom formula is
  4. Enter the formula: =COUNTIF($A$2:$A,A2)>1
  5. Click Done

This will highlight all duplicate values in the selected column or range.

Using the UNIQUE Function

  1. Select an empty column next to your data
  2. Enter the formula =UNIQUE(A2:A100) where A2:A100 is your data range
  3. Press Enter

This will extract only the unique values from the range, making duplicates easy to identify.

Using the Remove Duplicates Add-On

  1. Click Extensions > Add-ons
  2. Search for and install “Remove Duplicates”
  3. Highlight your data and click Remove Duplicates > Find Duplicates
  4. The add-on will highlight all duplicates

Counting Duplicate Values

Once you’ve identified the duplicates, you can count them using the COUNTIF function:

=COUNTIF(range, criteria)

For example, to count duplicates of “Apple” in column A:

=COUNTIF(A:A, "Apple")

This will count all cells containing “Apple”.

You can also use the Remove Duplicates add-on to count duplicates for you automatically.

Summing Duplicate Values

To sum duplicate values, use the SUMIF function:

=SUMIF(range, criteria, sum_range)

For example, if “Apples” are listed in column A, and quantities in column B:

=SUMIF(A:A, "Apple", B:B)

This sums all quantities in column B where the value “Apple” appears in column A.

Again, the Remove Duplicates add-on can also perform this calculation automatically.

Ignoring First Duplicate Occurrence

You may want to ignore the first occurrence of a duplicate when counting or summing.

To do this with COUNTIF:

=COUNTIF(range, criteria)-COUNTIF(range, criteria & "*")

The * indicates the first value.

For SUMIF, use OFFSET:

=SUMIF(range,criteria,OFFSET(sum_range,,,COUNTIF(range,criteria)-1))

Additional Tips

Here are some additional tips for working with duplicates in Google Sheets:

  • Use absolute references like $A$1 when creating conditional formatting rules. This allows you to copy/paste the rules to new data ranges without updating the references.
  • Name important ranges like “Data”!A1:D100 to make formulas easier to understand. Instead of A1:D100, reference the name “Data”.
  • Use the Remove Duplicates add-on to delete duplicate rows entirely, leaving only unique values.
  • When summing, ensure values are numbers by using VALUE() or double-clicking the cell and setting the number format. Text values will be ignored.
  • For more complex duplicate checking, advanced filter views or pivot tables may be required.

Conclusion

Identifying, counting, and summing duplicates is easy in Google Sheets using the built-in functions. Conditional formatting provides visual indicators of duplicates, while functions like COUNTIF and SUMIF can calculate metrics based on them. Add-ons provide additional options for managing duplicates. Following these tips will help you wrangle duplicate values in your spreadsheets.

Related Posts