Sum If Function

**Jiptastic** · 03-14-2013, 10:23 AM

Normally with the Sum If function you have the Sum column and the criteria column. For instance you want to average all the money made for Angelica: The name Angelica might be in Column A and the money in Column B. For my problem, however, I have them in the same cell. For instance, the cell reads---- "Angelica - $405".

How can I set a function to Sum all the cells that begin with "Angelica"?

**FDibbins** · 03-14-2013, 10:34 AM

short answer...you cant, not with just 1 formula. You will have to extract the value 1st, into another cell, and then you can use sumif() with a wild card

**Fotis1991** · 03-14-2013, 10:35 AM

sIMPLEST WAY IS TO USE TEXT TO COLUMNS, and split text and numbers in 2 different columns.

You can hide these 2 columns. Then use a SUMIF dunction.

**TMS** · 03-14-2013, 10:41 AM

Assuming that your text values were in column A from row 2 to row 7, a very specific solution for Angelica is:

=SUMPRODUCT(--(LEFT($A$2:$A$7,LEN("Angelica"))="Angelica"),--(MID($A$2:$A$7,FIND("$",$A$2:$A$7)+1,15)))

If you were to generalise it and put the name in cell B6, then this could be written:

=SUMPRODUCT(--(LEFT($A$2:$A$7,LEN(B6))=B6),--(MID($A$2:$A$7,FIND("$",$A$2:$A$7)+1,15)))

You will need to adjust the range to meet your requirements.

Regards, TMS