sumif combined text/number?

[MOB]

Some data I am using a sumif on has combined text and numbers, and I need to base it on just the number without using a helper column, eg;

DL15
DL5
MK15
MK5

My approach was =sumif(B:B,"*5",C:C)

However it will pick up all of the above examples, when I only want the result for DL5 & MK5 including.

Is there a simple way to amend my formula?

TIA

 

[jasonb75]

If it is always 2 letters followed by a number then I would try something like

=SUMPRODUCT(--((--MID(B2:B5,3,3))=5),C2:C5)

It will be less efficient, but I don't see a way to do what you want with sumif and no helper column.

 

[Fluff]

If it's always the 3rd character you're interested in, you could use

=SUMIF(B:B,"??5",C:C)

 

[MOB]

Thanks both

 

[Fluff]

Glad we could help & thanks for the feedback.