Sum Values of a cell that start with same text

[dioselinemenasha]

Hello, I am trying to set up a formula where I can sum up the values of a cell that start with a certain text.

Here is my spreadsheet, column I totals the values entered on the row (O3:no3) and subtracts from the vacation start total, but now I am also wanting to have column M total the values in that same row (o3:no3) that start with S, and column N to total the values that start with F.

I have tried the following formulas but neither work:

(SUMIF(O3:NO3,"S#", O3:NO3))

(SUM(IF(P3:NZ3="f#", "0#", P3:NZ3)))

I added conditional formatting so that when I enter S# the cell will change to blue color and when I enter F# it will change to blue so if you can assist with a formula that will grasp that and provide the totals that works for me too

Please help, I would like to avoid adding more rows since my table will already have 300 names to it

[#2 · Accepted solution]

@dioselinemenasha 

For example:

=SUM(IF(LEFT(O3:NO3)="S",--MID(O3:NO3,2,2)))

=SUM(IF(LEFT(P3:NZ3)="F",--MID(P3:NZ3,2,2)))

If you don't have Excel in Microsoft 365 or Office 2021, you'll have to confirm the formulas with Ctrl+Shift+Enter, to turn them into array formulas.

[#3]

@hansvogelaar Thank you, thank you, thank you so very much. I had been working on this formula for over a month and thanks to your assistance I was able to enter it within hours. While I learned a lot while researching excel's formula functions thru the month I wish I had reached out sooner for help. Stay healthy and again THANK YOU!