Extract numeric value from string and sum across range

[jjones312]

Hoping someone may help with the formula or a way to figure this out.

I need to calculate (sum) numeric values across a range after extracting the numeric value. see attachment for reference.

1. It is a calendar, each row is an individual employee
2. Employees can take a specific type of leave, in this case, FMLA
3. in their row, for the specific day, an entry is made in this format "F + number of hours using up to 8"
   1. example entries could be "F3", "F5", or "F8" as seen in the screenshot.
4. I need to be able to extract the numeric value and then sum it across the range which is 1 - 31 days in the month.

I can extract a since value using =MID(January[@15],FIND("f",January[@15])+1,1) but I'm not sure how to get to the next level and do it across the range.

Any assistance would be thankful.

 

[jasonb75]

Maybe

=SUMPRODUCT(IFERROR(--SUBSTITUTE(January[@[1]:[31]],"f",""),0))

Array confirmed with Ctrl Shift Enter if not using office 365.

 

[jjones312]

Maybe

=SUMPRODUCT(IFERROR(--SUBSTITUTE(January[@[1]:[31]],"f",""),0))

Array confirmed with Ctrl Shift Enter if not using office 365.

You are DA MAN!!!! I don't get on here often but when I do.. You guys so ROCK!!