 SOLVED

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

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

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.