While Google Sheets does not have a built-in SUMUNIQUEIFS function, there are effective workarounds to sum unique values based on multiple criteria. In this guide, we will explore two potent formulas that can help you achieve this.
Formula 1: Filter, then Unique the table including the sum column
This method is useful when you want to perform a conditional sum, excluding duplicate records, without removing them from the table temporarily.
Problem: Calculate the total quantity of all fruits in the north region, excluding duplicate records.
Criteria: B2:B = “Fruit” and D2:D = “North”
To use this formula, follow these steps:
- Filter the table range A2:D (excluding the header row) based on the specified criteria using the FILTER function.
- Assign a name, such as ‘range,’ to the filter formula using the LET function.
- Obtain the unique records in the ‘range’ using the UNIQUE function and assign a name, such as ‘u_range,’ to the formula using the LET function.
- Extract the third column in ‘u_range’ (the quantity column) using the CHOOSECOLS function and sum it using the SUM function.
This formula will return the desired result.
Formula 2: Filter, then Unique the table excluding the sum column
This SUMUNIQUEIFS workaround method is useful when you want to find the total of the first occurrence of records based on specific criteria.
Problem: Calculate the total quantity of all fruits in the north region, considering unique fruits (not unique records/rows).
Criteria: B2:B = “Fruit” and D2:D = “North”
To use this formula, follow these steps:
- Filter the table range A2:D (excluding the header row) based on the specified criteria using the FILTER function.
- Assign a name, such as ‘range,’ to the filter formula using the LET function.
- Return the unique records in the ‘range’ based on the fruits column using the SORTN function. Use the CHOOSECOLS function to select the fruit column for removing duplicates.
- Extract the third column in ‘u_range’ (the quantity column) using the CHOOSECOLS function and sum it using the SUM function.
This formula will return the desired result.
Practical Use of SUMUNIQUEIFS Workaround in Google Sheets
These two SUMUNIQUEIFS methods offer effective ways to conditionally sum tables that may contain duplicates. Duplicate records in a table can arise from various reasons such as data entry errors, joining tables, integrating data from various sources, collaborated sheets, multiple submissions of Google Forms data, and more.
When you need to perform conditional sum calculations on a table that requires cleanup, you can try these formulas. The first formula removes duplicate rows before summing from the selected range, while the second formula eliminates duplicates from your preferred non-criteria columns.
Implementing these formulas in your Sheets may require some basic understanding of Google Sheets functions.
For more resources on related topics, you can visit Crawlan.com.
Resources:
