[Solved] Need Excel Formula to get sum of all numbers in any cell with Specific text next to the number

[sweetbrwnshugar]

Hi everyone!

I’m a novice to coding but I actually need an Excel formula for my attendance tracker project.

I was wondering if anyone knows an EXCEL formula or even a VBA code that will give the total sum of any numbers within cell that has specific TEXT inside the same cell ?

For example I need to be able to type VAC8 into any range of cells within my spreadsheet to represent vacation time taken by the employee in the amount of 8 hours (or any other number), then have the sum of the hours for every specific VAC attendance code populate in another cell within the spreadsheet. I'd need to be able to sum the numbers next to all the different attendance codes. The attendance codes I'm using are below. I added the actual attendance tracker I'm trying to create.

Attendance Codes

VAC

FMLA

SL

FLT

WB

P-VAC

SVC

 

[shamimarita]

Hi everyone!

I’m a novice to coding but I actually need an Excel formula for my attendance tracker project.

I was wondering if anyone knows an EXCEL formula or even a VBA code that will give the total sum of any numbers within cell that has specific TEXT inside the same cell ?

For example I need to be able to type VAC8 into any range of cells within my spreadsheet to represent vacation time taken by the employee in the amount of 8 hours (or any other number), then have the sum of the hours for every specific VAC attendance code populate in another cell within the spreadsheet. I'd need to be able to sum the numbers next to all the different attendance codes. The attendance codes I'm using are below. I added the actual attendance tracker I'm trying to create.

Attendance Codes

VAC

FMLA

SL

FLT

WB

P-VAC

SVC

Hello Sweetbrwnshugar,

I hope you are doing well. I've used a combination of different functions. I applied it to all the attendance codes in your Excel file.
Note. Don't use the same code multiple times, try to use unique codes. Use VAC-P instead of P-VAC.

• Here is the formula: =SUM(IFERROR(VALUE(MID(N4:AJ42, FIND("VAC", N4:AJ42) + LEN("VAC"), 2)), 0))

Here is your workbook with the solution.

 

[sweetbrwnshugar]

Thank you so much Shamimarita this is exactly what I needed!