help again

[brilliantjimmyriddle]

I posted a msg earlier about counting hours on a payroll spread sheet. I was given this formula:
{=SUM(IF(ISERROR(MID(B3:H3,FIND("S",B3:H3)+1,255)),"",MID(B3:H3,FIND("S",B3:H3)+1,255)+0))}

which works great if I only have one code that uses "S." Unfortunately, I have several payroll codes that have "S" in them. Does anyone have a solution to my new problem?

Example,

jan 1 jan 2 jan 3 jan 4 jan 5
employee 1 S8 CSS8 W8 SA8 S8

If I want to count Sick hours, which is "S8" using the above formula, it wont work because of the Special Assignment code (SA) and the CSS code. Any suggestions would be appreciated.

 

[fairwinds]

=SUM(IF(ISERROR(MID(SUBSTITUTE(SUBSTITUTE(B3:H3,"CSS","X"),"SA","X"),FIND("S",SUBSTITUTE(SUBSTITUTE(B3:H3,"CSS","X"),"SA","X"))+1,255)),"",MID(SUBSTITUTE(SUBSTITUTE(B3:H3,"CSS","X"),"SA","X"),FIND("S",SUBSTITUTE(SUBSTITUTE(B3:H3,"CSS","X"),"SA","X"))+1,255)+0))

 

[brilliantjimmyriddle]

thank you so much!!!