How to Use UNIQUE and SUM together in Google Sheets

0
79
How to Use UNIQUE and SUM together in Google Sheets

In certain situations, it’s important to know the use of UNIQUE and SUM together in Google Sheets. Almost all spreadsheet users know the use of UNIQUE function. It’s to return unique values from a range. No matter what values, it can be text or numeric values. But there is a shortfall. See the below screenshot.

UNIQUE and SUM together in Google Sheets

Here what we want is, get the unique texts from Column A and Sum the corresponding values in Column B against it.

Here you can apply the UNIQUE formula in two ways. But none can return our desired result.

  1. Formula #1

=unique(A2:B5)

There is no point in using the above formula as it would return the data as it’s.

2. Formula # 2

=unique(A2:A5)

This has logic. But it only return the unique value from Column A. It won’t sum the values from Column B. So you should know how to use UNIQUE and SUM together in Google Sheets.

Steps to Combine UNIQUE and SUM together in Google Sheets

In normal case, we can’t use SUM with UNIQUE to get the correct result. We may require SUMIF in this case. Here we are going to combine UNIQUE and SUMIF together to get the result as below.

UNIQUE and SUMIF together in Google Sheets

Similar: How to Use SUMIFS Function in Google Sheets

Formula:

={unique(A2:A5),ArrayFormula(sumif(A2:A5,unique(A2:A5),B2:B5))}

Ho to develop this combined, UNIQUE and SUMIF, Google Sheets formula. Let me explain it.

Earlier I’ve followed a similar approach. That time with a combined SUMIF formula, that means multiple SUMIF together. Once you complete this tutorial, check that also.

Now to the steps:

The following is the first part of the formula. This’s a normal Unique formula to return the Unique texts from Column A.

Formula 1:

=unique(A2:A5)

The second part is a SUMIF formula with a slight variation from normal use. To make you clearly understand this formula, I am going to compare it with SUMIF syntax.

Formula 2:

Formula: =sumif(A2:A5,unique(A2:A5),B2:B5)

Syntax: SUMIF(range,criterion,sum_range)

In this formula, as you know, “Unique(A2:A5)” returns multiple values as criteria. So we should include ArrayFormula to include all the criteria.

join UNIQUE and SUMIF together in Google Sheets

Finally with the help of Curly Braces, I’ve combined Formula 1 and 2. This way you can use UNIQUE and SUM together in Google Sheets. But there is an alternative to this. You can use QUERY formula as below.

=query(A2:B5,”Select A, sum(B) group by A label Sum(B)””)

Learn Query formula Here.

Thanks for the stay. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here