Combine Similar Rows and Sum Values in Google Sheets

Query and Pivot Table are the two options in Google Sheets to combine and total duplicate/similar rows.

But you can also use a tricky Unique+Sumif combination formula.

In this Google Sheets tutorial, I am detailing how to combine similar rows and sum values in Google Sheets using both the Query and Unique+Sumif combo.

For the example purpose, I am using a basic set of data.

It’s not realistic-looking data. But with this, I hope I can well explain the formulas.

Sample Data:

Formula to Combine Similar Rows and Sum Values

I am only using two-column data in the first example.

In the last part of this tutorial, you can learn how to use multiple columns in similar calculations.

Combining duplicate rows and sum values in Google Sheets is not a difficult task.

Basic Query or Sumif knowledge is required for this.

Even if you don’t know how to use Query, you may go through my formula to gain the basic skill to combine similar rows and sum values in Google Sheets.

Query Formula to Combine Similar Rows and Sum Values in Google Sheets

Formula:

=query(B3:C11,"Select B, Sum(C) group by B")

Suppose you may want to include an unlimited number of rows in the Query to cover future values.

In that case, modify the above Query as below.

=query(B3:C,"Select B, Sum(C) where B is not null group by B")

Note:- The above two Query formulas that combine duplicate rows are based on the sample data above (screenshot).

Now see how to combine duplicate rows in Google Sheets and sum the value column using another formula. I am using Unique and Sumif this time.

Formula:

=index(sumif(B3:B11,unique(B3:B11),C3:C11))

OR use;

=ArrayFormula(sumif(B3:B11,unique(B3:B11),C3:C11))

The above two formulas would only return the sum of duplicates.

So first, use the below Unique formula in one cell and either of the above two formulas in another cell.

Use the above formula in cell G4 and the following formula in cell F4.

=unique(B3:B11)

The role of the Index in the first formula and the ArrayFormula in the second formula is the same.

What’s that?

Since the Sumif can’t return an array result, I mean multiple values, we must use an Array Formula with it.

Note:- Don’t forget to use the keyword “Duplicates” to find several duplicate removal tips on this site.

Combines Duplicates in Rows and Sum the Values (Multiple Column Duplicates)

First, see my sample Google Sheets data and the formula output.

First, see my sample Google Sheets data and the formula output.

Here Query is the proper solution.

=query(B3:D,"Select B,C, sum(D) where B is not null group by B,C")

This time, if you want, you can use Sumif as below.

The Unique Formula in cell G4:

=UNIQUE(B3:C8)

The Sumif Formula in cell I4:

=ArrayFormula(sumif(B3:B8&C3:C8,G4:G7&H4:H7,D3:D8))

Conclusion

In my examples, I recommend the formulas based on Query to my readers.

I have just included the Sumif to make you understand the flexibility of the functions available in Google Sheets.

I hope you have enjoyed your stay.

15 COMMENTS

Chad August 28, 2023 At 2:02 pm

This query formula opened up a whole new world to me. Thanks!

ching May 4, 2022 At 10:17 am

Hi, I’m working on similar to it.

I need to combine a duplicate row and sum the values of 2 columns differently.

After getting that, I need to combine my filter function with the query function.

Filter Function:

=filter(C5:E,A5:A=B1) [this is for the dropdown category]

- Prashanth May 5, 2022 At 7:58 pm
  
  Hi, ching,
  
  I’m not clear. Please feel free to share an example (sheet).
  
Janira Iglesias October 20, 2021 At 3:15 am

Hello,

I am doing something similar, but I need to separate the data by date. I cannot seem to get the formula right. Can you assist?

- Prashanth October 20, 2021 At 12:31 pm
  
  Hi, Janira Iglesias,
  
  Here is an example.
  
  Data in Columns:
  
  A – Dates
  B – Item Description
  C – Quantity.
  
  The formula in D1:
  
  =query(A1:C,"Select A,B,sum(C) where A is not null group by A,B")
  
  Please try it first. If that doesn’t help, make a sample and share that Sheet’s address/URL via the comment below.
  
Felix August 20, 2020 At 10:04 pm

Hi Prashanth,

hope you are well. First of all thanks so much for sharing your knowledge with the world. Very highly appreciated.

I would have one question sum values part. I used your Merge/Combine rows solutions and it’s working fine for my use case. Except for one part.

I’m using it for consolidation Orders from an e-commerce shop so I get one row per Order showing all different order positions and items that are in one basket/order.

So origin Data shows two rows when two items are ordered. In one column it says “order position number” ..and because it’s two items its two rows with “order position number” #1 and the next row #2.

I would like to not sum up but only show in the final table “order positions: #2” (as there are two items). Summing up would show #3.

So is there a way to either only take the second value = #2 or identify the highest value? (it could also be somebody orders 5 times the same item, then I would have 1 row for the order showing order positions =5)

- Prashanth August 21, 2020 At 10:43 am
  
  Hi, Felix,
  
  Please feel free to share a mockup (sample) sheet. I think I can help you write the required formula.
  
Louie N May 6, 2020 At 1:24 am

Hello, are you still taking inquiries? I am trying to consolidate two sets of information that have repeating values (as shown in your video) but I would like a different result. Are you still monitoring this?

Thanks!

- Prashanth May 6, 2020 At 11:49 am
  
  Hi, Louie N,
  
  Nope! But I’ll try to help you in my leisure if you can share with me a sample of what you are working on. Use the ‘Reply’ below to share yours sheet. The link won’t be published here to protect your privacy.
  
  NB: Just a sample in 5-10 rows and a few columns.
  
Boss November 17, 2019 At 5:26 pm

Need help! The array formula calculates all blank cells too. How to get rid off it?

- Prashanth November 18, 2019 At 2:00 pm
  
  The below formula is as per the sample data and the formula used in the example in my tutorial.
  
  =ArrayFormula(if(len(G4:G),sumif(B3:B&C3:C,G4:G&H4:H,D3:D),))
  
  In your case, it might be len(K2:K)
  
  Best,
  
Adam B. Yuro August 23, 2019 At 8:03 pm

Hey Prashanth,

I’m still having issues doing this. Since I can’t post a screenshot, I will try to explain it here.

Column C has Multiple Values (Will Continue to grow with different Values)
Column D has Multiple Values (Will Continue to grow with different Values)
Column E is what I need the SUM for and is numeric

Thank you in advance,

Adam B. Yuro

- Prashanth August 24, 2019 At 2:42 pm
  
  Hi, Adam,
  
  You can try this Query. In this row#1 is considered as the header row.
  
  =query(C1:E,"Select C,D, sum(E) where C is not null group by C,D",1)
  
  If there is no header, then the Query formula would be like this.
  
  =query(C1:E,"Select C,D, sum(E) where C is not null group by C,D",0)
  
  Both of these formulas won’t work if you are from EU countries. Then use the Query as below.
  
  =query(C1:E;"Select C,D, sum(E) where C is not null group by C,D";1)
  
  If not helpful? Then try to share your Sheets’ link (no personal/confidential data)
  
  Best,
  
mark j. June 20, 2019 At 1:47 am

This is great and thank you! Is there a way to not make the resulting data a new set of columns, but rather rearrange and modify the original data to show the sums?

- Prashanth June 20, 2019 At 10:40 am
  
  Hi, Mark J.,
  
  Please share the screenshot of your example data and the expected result.