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

sweetbrwnshugar

New member
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
 

Attachments

  • Sweetbrwnshugar Attendance Tracker.xlsx
    18.7 KB · Views: 2
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))
Formula.png

Here is your workbook with the solution.
 

Attachments

  • Sweetbrwnshugar Attendance Tracker.xlsx
    21.2 KB · Views: 2

Online statistics

Members online
0
Guests online
14
Total visitors
14

Forum statistics

Threads
311
Messages
1,378
Members
568
Latest member
WilliamHon
Top