SUMIF reference cell is text only

Is there a way to SUMIF cells in the reference column do not contain any numbers?

I am successfully using the below to extract and sum numbers from the reference column (I), but I also need to sum the numbers in column B if column I contains the text in A47 (and other text) without any numbers. Sample is attached.


{=SUM(IFERROR(IF(ISNUMBER(FIND(A47,$I$4:$I$44)),VALUE(LEFT($I$4:$I$44,FIND(A47,$I$4:$I$44)-1)),0),0))}

Do you expect 6 in B13, only the sum of the red numbers in column B?

In that case please try in B13

Formula:

Please Login or Register  to view this content.

Result is 6.

In the sample sheet A47 is empty.
I don't quite understand what exactly should happen with A47 if that cell is or is not filled. Can you give some examples of that?

Thanks! I was actually trying to reduce B13 by the red numbers, so subtracting your formula from the broader sum formula as below works perfectly for the example. (Sorry about the confusion with cell A47, it was A13 on the sample; I pasted the formula before redacting my workbook into a sample size.)

=SUMIF($I$4:$I$10,"*"&A13&"*",$B$4:$B$10)-SUMIF($I$4:$I$10,"?*"&A13&"*",$B$4:$B$10)

NOTE FOR OTHERS: This solution works based on the layout of my text (single character preceding reference text) and is not generally applicable to all situations that the title references.

Good to see you were able to use my share of the solution. Would you consider adding reputation if you think the answer is worth it?

Thx for the rep.