Data > Data cleanup > Remove duplicates deletes repeated rows, but it does not add their numbers together. To combine duplicate rows into one and sum the amounts, you need a formula. This page shows four ways, from the shortest to the most flexible, and how to decide whether to keep the result live or paste it as values.
Row 1 holds headers. A2:C6 (Customer, Region, Amount):
Acme, East, 100
Globex, West, 50
Acme, East, 25
Acme, West, 10
Globex, West, 70
Wanted result: one row per customer with the total amount.
Put this in E1, with the header row included in the range:
=QUERY(A1:C6,"select A, sum(C) where A is not null group by A label sum(C) 'Total'",1)
Result: Customer, Total, then Acme 135 and Globex 120. The columns in select must either be in the group by or be wrapped in an aggregate such as sum, so you cannot select B here without grouping by it. The where A is not null part keeps blank rows out of the result. QUERY updates by itself as you add rows, if your range covers them (for example A1:C1000 instead of A1:C6).
This is easier to read and edit if you do not like QUERY syntax. In E2:
=UNIQUE(A2:A6)
That lists Acme and Globex. In F2:
=ARRAYFORMULA(IF(E2:E="","",SUMIF($A$2:$A$6,E2:E,$C$2:$C$6)))
F2:F3 gives 135 and 120. If you prefer a plain formula, use =SUMIF($A$2:$A$6,E2,$C$2:$C$6) in F2 and fill it down.
If Acme East and Acme West must stay separate, the key is the Customer and Region pair. List the unique pairs in E2, which fills E2:F4:
=UNIQUE(A2:B6)
That returns Acme East, Globex West and Acme West, in order of first appearance. In G2, fill down to G4:
=SUMIFS($C$2:$C$6,$A$2:$A$6,E2,$B$2:$B$6,F2)
Results: 125, 120, 10. The QUERY version of the same thing groups by both columns:
=QUERY(A1:C6,"select A, B, sum(C) where A is not null group by A, B label sum(C) 'Total'",1)
Result: Customer, Region, Total, then Acme East 125, Acme West 10, Globex West 120.
Sometimes you do not want to sum a column but collect it, for example the regions per customer. Using the customer list from Method 2 in E2:E3, put this in G2 and fill down:
=TEXTJOIN(", ",TRUE,FILTER($B$2:$B$6,$A$2:$A$6=E2))
For Acme this gives East, East, West, which keeps repeats. Wrap FILTER in UNIQUE to drop them:
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER($B$2:$B$6,$A$2:$A$6=E2)))
Acme gives East, West and Globex gives West. TRUE tells TEXTJOIN to skip empty values. QUERY cannot join text, so use this method for that job.
The formulas above are live. When the source data changes, the totals update. That is what you want for a reporting tab. Things to know:
The result spills into the cells below and to the right. If anything sits in the way, you get a #REF! error saying the array result was not expanded. Clear the cells in its path.
Leave your source data alone and build the merged result on another tab.
To freeze the result, select it, copy it, then Edit > Paste special > Values only (Ctrl+Shift+V). The numbers stay, the formulas go, and you can sort or edit them freely. Do this on a copy of the tab if you intend to delete the original rows.
Amounts stored as text. SUMIF and QUERY ignore text, so the sum is too small or 0. Check =ISNUMBER(C2), and convert with VALUE or Data > Data cleanup.
Trailing spaces. "Acme " and "Acme" are separate keys, so you get two rows. Use Data > Data cleanup > Trim whitespace first.
Spelling variants. "Acme Ltd" and "Acme Limited" are different keys to every formula here.
Blank rows. UNIQUE over a long range such as A2:A1000 returns one blank entry, which is why Method 2 tests for "".
Case. SUMIF and SUMIFS ignore case, so "acme" and "ACME" are summed together.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
Every formula here treats the key as exact; if your duplicates are the same customer spelled differently, the Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas can score the similarity so you can build a clean key first, and for clean keys you do not need it.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Need to catch near-duplicates that exact formulas miss, such as "Acme Pty Ltd" and "ACME Pty. Ltd"? The Data Cleanup Kit for Google Sheets (US$15, no add-on install) groups them for you with FUZZYDUPES, and adds ready-made tabs for merging and summing, keeping the latest row, comparing lists, splitting names and cleaning text: https://snakback.gumroad.com/l/data-cleanup-kit
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.