# Formula to total sum of cell values when cells contain both text & numbers

1. ## Formula to total sum of cell values when cells contain both text & numbers

I have a speadsheet to keep a track of staff shift patterns and leave periods. A number of the cells contain text & numbers and I want to total the sum of the number values that contain certain text. I have attached a sample of the spreadsheet. I have used the array formula {=SUM(IF(LEFT(D6:U6)="A", --RIGHT(D6:U6,LEN(D6:U6)-1)))} in cell Y6 to calculate the number of hours annual leave taken in ROW6 and this formula works fine. However if I use the array formula {=SUM(IF(LEFT(D6:U6)="LD", --RIGHT(D6:U6,LEN(D6:U6)-1)))} in cell AG6 to calculate the number of hours lieu days taken the result is remaining as 0 instead of 12. I get the same result using the array formula {=SUM(IF(LEFT(D6:U6)="PS",--RIGHT(D6:U6,LEN(D6:U6)-1)))} in cell AI6 to calculate the number of hours paid special leave taken.

Can anybody suggest a solution?  Register To Reply

2. ## Re: Formula to total sum of cell values when cells contain both text & numbers

In AG6

=SUM(IF(LEFT(D6:U6,2)="LD",--RIGHT(D6:U6,LEN(D6:U6)-2),0))

Same correction for any 2 character checks e.g. "PS"  Register To Reply

3. ## Re: Formula to total sum of cell values when cells contain both text & numbers

Thanks for the solution, works exactly as I wanted.  Register To Reply

4. ## Re: Formula to total sum of cell values when cells contain both text & numbers

Thanks for the feedback.  Register To Reply