# Extract numeric value from string and sum across range

#### jjones312

##### New Member
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.

#### Attachments

• fmlaa.png
31.4 KB · Views: 21

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### jasonb75

##### Well-known Member
Maybe
Excel Formula:
=SUMPRODUCT(IFERROR(--SUBSTITUTE(January[@[1]:[31]],"f",""),0))
Array confirmed with Ctrl Shift Enter if not using office 365.

#### jjones312

##### New Member
Maybe
Excel Formula:
=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!!

Replies
18
Views
520
Replies
8
Views
454
Replies
5
Views
726
Replies
10
Views
272
Replies
2
Views
100

1,191,718
Messages
5,988,271
Members
440,146
Latest member
rgomes8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back