Sum Values of a cell that start with same text

New Contributor

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 Replies
best response confirmed by allyreckerman (Microsoft)


For example:



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.


@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!