View Full Version : Solved: Sum an unordered group of rows by a common String

03-10-2011, 08:48 AM
I've got a en excel book with about 2000 rows. Each row has a 3 character code and some numbers. The codes aren't ordered alphabetically; similar codes may occur randomly throughout the worksheet.

I need to group these codes together into one row, hence I need to sum up all the numbers in these rows as well.

I've attached a sample workbook. Any help is appreciated

03-10-2011, 05:57 PM

Based on the data in your workbook:

Detach/open workbook BobBarker - VE36506 - SDG10.xls and see the formula in cell F3.

Have a great day,

03-11-2011, 09:07 AM
This is excellent! Thank you very much stanleydgrom :)
I had a bloated solution by creating a pivot table and using a vlookup to fetch data from the pivot table - this is much more elegant.

03-11-2011, 09:18 AM
FYI, you can also use a wild card as in
=SUMIF($A:$A,$E3 & "*",B:B)
Ths would look for similar items such as AAA01, AAA02, AAA03 etc

03-11-2011, 09:21 AM
Duly noted. Thanks for the tip.

PS: I still haven't figured out how to mark a topic as solved :/
[edit] Found it! It was hidden in Thread Tools :)

Bob Phillips
03-11-2011, 09:26 AM
You can also build the list dynamically.

Assuming you have Code in E1, enter this array formula in E2


and copy down

03-11-2011, 09:53 AM
I like the concept and I'm trying it against stanleydgrom's excel file, but the output is always a blank cell.

Bob Phillips
03-11-2011, 03:50 PM
Did you array-enter it?

Also, stanley's file has a row 1 that I removed.