# Sum a row with cells that may be blank, and some rows that have letters and numbers.



## rowrow780 (Dec 26, 2022)

I am creating a vacation and sick tracker. Vacation identified by V. The number of hours will be after the letter V. . 
I have tried a formula that has worked when there is no blanks however I have an error when there is blanks. There are blanks as we are only entering Vac hours on the cells ( represents the calendar days ) an employee takes the time.
How would I do this formula to sum total V hours? 
Thank you!


----------



## Eric W (Dec 26, 2022)

Try:

Book1ABCDEFGHIJKLMNO1JanuarySuMTWThFSaSuMTWThFSa2Employee1234567891011123Test UserV5v5v245Sum of vacation612Sheet3Cell FormulasRangeFormulaA6A6=SUM(IF(LEFT(B3:M3)="v",MID(B3:M3,2,5)+0))


----------



## rowrow780 (Dec 26, 2022)

Eric, you just saved me hours! Thank you so very much!!!!


----------



## Eric W (Dec 26, 2022)

Happy to help!  Thanks for the update.


----------



## rowrow780 (Dec 28, 2022)

Eric W said:


> Happy to help!  Thanks for the update.


Hi Eric, i've run into a scenerio..  
what formula would i use to sum the total hours if an employee will take a combination of sick and vac hours?. Example : v5 ( vacation ) and s3 (sick ) in one day . 

TIA!


----------



## Sufiyan97 (Jan 2, 2023)

Try until Eric enters, not too good but works

Book7ABCDEFGHIJKLMNO1JanuarySuMTWThFSaSuMTWThFSa2Employee1234567891011123Test UserV5v5 s3v245Sum of vacation61578Sheet2Cell FormulasRangeFormulaA6A6=SUM(IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3:M3,"v",0),"V",0),"S",0),"s",0),SEARCH(" ",B3:M3&" ")-1)+0,0))+SUM(IFERROR(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3:M3,"v",0),"V",0),"S",0),"s",0),SEARCH(" ",B3:M3),5)+0,0))


----------



## Eric W (Jan 2, 2023)

Thanks Sufiyan97 for stepping in, I kind of lost track of this one.  Here's another option:

Book1ABCDEFGHIJKLMNO1JanuarySuMTWThFSaSuMTWThFSa2Employee1234567891011123Test UserV5v5 s3s1 v4v245Sum of vacation62078Vacation169Sick410Total20Sheet2Cell FormulasRangeFormulaA6A6=SUM(IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3:M3,"v",0),"V",0),"S",0),"s",0),SEARCH(" ",B3:M3&" ")-1)+0,0))+SUM(IFERROR(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3:M3,"v",0),"V",0),"S",0),"s",0),SEARCH(" ",B3:M3),5)+0,0))B8B8=SUM(MID(B3:O3&"v0",SEARCH("v",B3:O3&"v")+1,1)+0)B9B9=SUM(MID(B3:O3&"s0",SEARCH("s",B3:O3&"s")+1,1)+0)B10B10=SUM(B8:B9)

This assumes that the number of hours is always 1 digit.  If you want, you can combine the B8 and B9 formulas into one cell easy enough.


----------



## rowrow780 (Jan 2, 2023)

Thank you Eric and Sufiyan! appreciate this!


----------

