PDA

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

BobBarker
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

stanleydgrom
03-10-2011, 05:57 PM
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
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.

mdmackillop
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

BobBarker
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 :/
 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

=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
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.