SUMIF/SUMIFS Excluding Duplicate Values in Google Sheets

Published on

Discussing how to use SUMIF/SUMIFS while excluding duplicates in Google Sheets is important, but duplicates should not be a problem in most cases.

These functions are typically employed to calculate the sum of a column based on the occurrences of specific criteria in another column.

These occurrences can be considered duplicates, right? Therefore, there’s no issue of uniqueness or duplicates when using SUMIF/SUMIFS in the usual manner.

Before we dive into how to use SUMIF or SUMIFS while excluding duplicates in Google Sheets, it’s essential to understand this concept.

You should first understand what we mean by duplicates in the context of SUMIF/SUMIFS in Google Sheets.

Duplicates in SUMIF/SUMIFS Functions: What They Are and How to Exclude Them

For example, consider a situation where column A contains items, column B contains ordered quantities, column C contains customer names, and column D contains purchase order numbers.

Sample Data: SUMIF/SUMIFS Excluding Duplicates

Consider the item “Black Sand 3/16,” which received four orders from two customers. Each customer ordered the item twice. However, due to a material shortage, we will only consider their first order.

So, the purpose of SUMIF/SUMIFS here is to get the total of the item “Black Sand 3/16,” without duplicates w.r.t. customer.

Using Regular Formulas

We typically use the following formulas when we want to obtain the total orders for the item “Black Sand 3/16”:

  • To get the total order of the item “Black Sand 3/16” (regardless of customer):
    • =SUMIF(A2:A, "Black Sand 3/16", B2:B) // returns 675
    • =SUMIFS(B2:B, A2:A,"Black Sand 3/16") // returns 675
  • If we want to get the total order of the item “Black Sand 3/16” specifically for “Customer 1”:
    • =SUMIFS(B2:B,A2:A,"Black Sand 3/16",C2:C,"Customer 1") // returns 225

Let’s dive into how to use SUMIF or SUMIFS while excluding duplicates in Google Sheets.

Excluding Duplicates in SUMIF/SUMIFS

Now, I’m going to discuss the topic of dealing with duplicates in SUMIF/SUMIFS calculations.

The objective is to eliminate multiple instances of the criterion based on a condition in another column.

In other words, we want to remove multiple orders of “Black Sand 3/16” from the same customer. Considering the structure of our sample data, we need to address duplicates in columns A and C.

We have two methods to achieve SUMIF/SUMIFS excluding duplicates in Google Sheets: one with a helper column and one without. We will explore both solutions.

Note: This won’t work in Excel because one of the functions used here was not available in Excel as of the last update of this post.

Using SUMIF to Exclude Duplicates in Google Sheets

Using a Helper Column

I assume the above sample data is in Sheet1!A1:D.

Enter the following SORTN formula in Sheet2!A1.

=SORTN(Sheet1!A2:D,9^9,2,Sheet1!A2:A&Sheet1!C2:C,1)

The SORTN function is used to eliminate duplicates. It returns unique values based on two columns, which are columns A and C.

Syntax of the SORTN Function in Google Sheets:

SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …])

Where:

  • Sheet1!A2:D: This is the range of data from which you want to remove duplicates.
  • 9^9: This number is used as an arbitrarily large number to ensure that all rows are included after processing.
  • 2: This display_ties_mode number indicates that you want to remove duplicates.
  • Sheet1!A2:A&Sheet1!C2:C: This part is the key to the formula. It concatenates the values from columns A and C for each row. The repetition of these values in subsequent rows will be considered as duplicates.
  • 1: This argument specifies the sort order, with 1 indicating ascending order.

Now you can use a regular SUMIF/SUMIFS formula in this new data which contains unique records w.r.t. items and customers.

=SUMIF(A1:A,"Black Sand 3/16", B1:B)
=SUMIFS(B1:B, A1:A, "Black Sand 3/16")

This will be equivalent to excluding duplicates in the original data when using SUMIF/SUMIFS.

Formula Method Without a Helper Column

SUMIFS, as well as SUMIF, won’t work with virtual columns in the range of sum. Therefore, it’s evident that we can’t utilize the above data within these functions.

As an alternative, we can use the following QUERY:

=QUERY(SORTN(Sheet1!A2:D, 9^9, 2, Sheet1!A2:A&Sheet1!C2:C, 0), "select sum(Col2) where Col1='Black Sand 3/16' label sum(Col2)''")

This QUERY calculates the sum of column 2 where the value in column 1 is equal to “Black Sand 3/16.”

So QUERY is an alternative to SUMIF/SUMIFS excluding duplicates in Google Sheets.

Conclusion

Your need to SUMIF/SUMIFS excluding duplicates may differ from the example discussed above. Nevertheless, you can consider this as a starting point.

Feel free to describe your specific requirements in the comments below. I will personally review each relevant comment.

Related: Mastering Unique Summing in Google Sheets Without SUMUNIQUEIFS.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.