Find HW in a range and total the number that comes after an HW

[impuce]

Hi,

I have a spreadsheet that has numbers and "HW"numbers (HW02, HW07...) in the cells.
I need a formula that will find the HW in a range of cells and total the numbers that follows the HW.

I've tried this formula: SUMPRODUCT(TEXT(MID(B2:G9,FIND("HW",B2:G9&"HW")+2,3),"0;;;\0")) but it doesn't work. I've attached a sample image of what I'm looking for.

Thanks in advance.

 

[impuce]

Fluff is 100% right ...!!!

To be on the safe side

=SUMPRODUCT((LEFT(B2:B9,2)="HW")*(SUBSTITUTE(B2:B9,"HW","")*1))

That gives me #VALUE! error. The first formula you gave me works.