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

[Rogerjw]

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?

[JohnTopley]

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"

[Rogerjw]

Thanks for the solution, works exactly as I wanted.

Many thanks for your help.

[JohnTopley]

Thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.