Excel 2007 : replace, sum and filter

Hi, so I need help with find and replace formula but also with filtering at the same time. In the attachement in sheet1 there is data, in sheet2 there is an automatic copy of results from sheet1 and in column 3 of sheet 2 are numbers associated with particular row. In column G I have a list of cells I want to look up in column A and replace it with corresponding text in column h. Columns J and K show already replaced text with sum of numbers each cell had, for example "Mom" has both "aaa" and "ggg" so we have 6+1+8 for aaa and 5 for ggg which equals 20 overal. In column J I have kkk because it isn't in G column, so it stays as it is, but still is summing all the numbers.

Thanks

PS. There is mistake in J2:J5, it should be brother instead of cc and so on except kkk. Sorry

Not entirely sure I follow but could you not use a Pivot ?

Using your example...

Sheet2!A1: field1
Sheet2!B1: field2
Sheet2!C1: field3

Sheet2!C2:
=IFERROR(VLOOKUP($A2,$G:$H,2,0),$A2)
copied down to C16

Create a Pivot based on A1:C16 (you can omit A1:A16 but makes sense to include for flexibility)

Set the Pivot up such that field3 is Row Label and field2 is Data Field.
Order Row Label based on Sum of Field2 desc.

That would mirror your expected results.

If you want to omit those values for which no "replace" value exists you can change the IFERROR and replace the 2nd instance of $A2 to "exclude" ... you can then de-select "exclude" item from resulting pivot.

NOTE:

IFERROR is new to XL2007 (and beyond) -- your profile implies 2003 however your attachment implies 2007 or later.