This is not a duplicates problem. The strings genuinely differ, so nothing that compares text exactly will ever catch them.
What you need is a group label. Every row that means the same thing gets the same canonical name, and then SUMIF and pivot tables work again.
The Old Way (Wildcards and Edit Distance)
The first attempt is usually a wildcard lookup on a shared prefix.
=VLOOKUP(LEFT(A2, 5) & "*", $A$2:$A$100, 1, FALSE)Run it on a column containing Acme Corp, The Acme Corp, Acme Corporation, and Acme Systems Inc. The row for The Acme Corp returns itself, because LEFT("The Acme Corp", 5) is The A. A leading article is enough to strand a row in its own group of one.
The second attempt is containment counting.
=COUNTIF($A$2:$A$100, "*" & A2 & "*")This fails in the other direction. A short value like Inc matches Zinc Inc and Incline Media, so you get false groups instead of missing ones.
The advanced answer everybody recommends is pasting a Levenshtein edit distance function into Apps Script. That has a real scaling wall. Edit distance is a pairwise comparison, so 2,000 rows means four million comparisons. That runs into the six minute Apps Script execution limit, and a custom function called from every cell locks the sheet during recalculation.
Edit distance also gets the semantics wrong. It scores John Smith and Smith, John as very different because word order changed. It scores IBM and International Business Machines as completely unrelated. Those are exactly the pairs you were trying to group.
The FITS Way (Assign a Canonical Name)
Stop comparing rows to each other. Ask FITS to normalize each row to a canonical form, then group on that column with the tools you already know.
=FITS("Return the canonical company name for this entry. Drop leading articles, legal suffixes like Inc, Corp, and LLC, and punctuation. Return the name only: " & A2)Acme Corp, The Acme Corp, and Acme Corporation all return Acme. Acme Systems Inc returns Acme Systems, which is correct, because it is a different company.
Cost is linear here, not quadratic. Each row is evaluated once instead of against every other row, so this does not degrade the way an edit distance script does.
Word order and acronyms both fall out for free, since the formula reads meaning rather than character positions.
=FITS("Return this person name as Firstname Lastname, regardless of the order or punctuation it arrived in: " & A2)If you have an approved list of groups, constrain the answer to it. That keeps the output stable enough to join on.
=FITS("Match this entry to the closest name in the approved list. Answer with the list entry exactly, or UNMATCHED if none is close. Entry: " & A2 & " | List: " & TEXTJOIN(", ", TRUE, Accounts!A2:A))Then group the normalized column the ordinary way.
=SUMIF($B$2:$B, B2, $C$2:$C)For a quick view of how many distinct groups you actually have, run UNIQUE on the canonical column. That number is usually a lot smaller than the raw row count, which is the point.
When to Use Each
If the values differ only by capitalization or stray spaces, ordinary cleanup is faster and cheaper. Reach for =FITS() when the strings differ by real words, by word order, or by abbreviation. The related jobs use the same shape: removing duplicates that differ only by case, classifying free text into fixed categories, and replacing text without nested SUBSTITUTE calls. The full tour is in automating Google Sheets tasks you used to need regex for.
Stop Pasting Levenshtein Scripts
FITS puts plain-English AI formulas inside Google Sheets. Give every near-duplicate one canonical name and let your pivot tables do the rest. Free tier included.