SOLVED

Sum Values of a cell that start with same text

%3CLINGO-SUB%20id%3D%22lingo-sub-2863078%22%20slang%3D%22en-US%22%3ESum%20Values%20of%20a%20cell%20that%20start%20with%20same%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2863078%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20trying%20to%20set%20up%20a%20formula%20where%20I%20can%20sum%20up%20the%20values%20of%20a%20cell%20that%20start%20with%20a%20certain%20text.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dioselinemenasha_0-1634676190275.png%22%20style%3D%22width%3A%20747px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318537iCC909244BE3AFE69%2Fimage-dimensions%2F747x99%3Fv%3Dv2%22%20width%3D%22747%22%20height%3D%2299%22%20role%3D%22button%22%20title%3D%22dioselinemenasha_0-1634676190275.png%22%20alt%3D%22dioselinemenasha_0-1634676190275.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHere%20is%20my%20spreadsheet%2C%20column%20I%20totals%20the%20values%20entered%20on%20the%20row%20(O3%3Ano3)%20and%20subtracts%20from%20the%20vacation%20start%20total%2C%20but%20now%20I%20am%20also%20wanting%20to%20have%20column%20M%20total%20the%20values%20in%20that%20same%20row%20(o3%3Ano3)%20that%20start%20with%20S%2C%20and%20column%20N%20to%20total%20the%20values%20that%20start%20with%20F.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20following%20formulas%20but%20neither%20work%3A%3C%2FP%3E%3CP%3E(SUMIF(O3%3ANO3%2C%22S%23%22%2C%20O3%3ANO3))%3C%2FP%3E%3CP%3E(SUM(IF(P3%3ANZ3%3D%22f%23%22%2C%20%220%23%22%2C%20P3%3ANZ3)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20added%20conditional%20formatting%20so%20that%20when%20I%20enter%20S%23%20the%20cell%20will%20change%20to%20blue%20color%20and%20when%20I%20enter%20F%23%20it%20will%20change%20to%20blue%20so%20if%20you%20can%20assist%20with%20a%20formula%20that%20will%20grasp%20that%20and%20provide%20the%20totals%20that%20works%20for%20me%20too%3C%2FP%3E%3CP%3EPlease%20help%2C%20I%20would%20like%20to%20avoid%20adding%20more%20rows%20since%20my%20table%20will%20already%20have%20300%20names%20to%20it%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2863078%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2863202%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Values%20of%20a%20cell%20that%20start%20with%20same%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2863202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1190195%22%20target%3D%22_blank%22%3E%40dioselinemenasha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%3A%3C%2FP%3E%0A%3CP%3E%3DSUM(IF(LEFT(O3%3ANO3)%3D%22S%22%2C--MID(O3%3ANO3%2C2%2C2)))%3C%2FP%3E%0A%3CP%3E%3DSUM(IF(LEFT(P3%3ANZ3)%3D%22F%22%2C--MID(P3%3ANZ3%2C2%2C2)))%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20have%20Excel%20in%20Micorsoft%20365%20or%20Office%202021%2C%20you'll%20have%20to%20confirm%20the%20formulas%20with%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

dioselinemenasha_0-1634676190275.png

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)
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.

 

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