Extract numeric value from string and sum across range

jjones312

New Member
Joined
May 17, 2013
Messages
31
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
    fmlaa.png
    31.4 KB · Views: 7

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
Maybe
Excel Formula:
=SUMPRODUCT(IFERROR(--SUBSTITUTE(January[@[1]:[31]],"f",""),0))
Array confirmed with Ctrl Shift Enter if not using office 365.
 
Solution

jjones312

New Member
Joined
May 17, 2013
Messages
31
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!!
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,020
Latest member
LongDoo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top