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

[BobBarker]

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

[stanleydgrom]

BobBarker,

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,
Stan

[BobBarker]

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.

[mdmackillop]

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

[BobBarker]

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]

You can also build the list dynamically.

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

=IF(ISERROR(MATCH(0,COUNTIF(E$1:E1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(E$1:E1,$A$1:$A$20&""),0)))

and copy down

[BobBarker]

I like the concept and I'm trying it against stanleydgrom's excel file, but the output is always a blank cell.

[Bob Phillips]

Did you array-enter it?

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