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 :/
[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
=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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.