formula needed

[Guest]

Hello,
I have a spreadsheet that calculates our attendance, time
off, overtime etc. and I need to count the following:
If ST1 OR ST2 OR ST3 (this stands for 'straight time' and
the number of hours worked) is typed in a cell, I need a
formula to add these cells. (example if a1 is = to st1
count etc.)
example

st3 is typed in a cell and st4 is typed in the next cell
the total would = 7 (I need the formula to add st3 & st4)
Thanks,

Cheryl

 

[Roger Govier]

Hi

One way would be to use the array formula
{=SUM(MID(A1:A24,3,2)*1)}

Do not enter the { } curly braces yourself.
Hold down Control + Shift as you press Enter after typing the formula and
Excel will insert the curly braces for you.

Change range to suit.

 

[Biff]

That formula fails if the number is more than one digit -
st12. Try this array formula:

=SUM(--MID(A9:A10,FIND("st",A9:A10)+2,255))

Find() is case sensitive. You could use SEARCH() which is
not case sensitive.

Biff

 

[Biff]

OOPS! I meant to say that formula fails if the number is
more than 2 digits - st120. Using the FIND with the 255
parameter pretty much will cover any situation you may
encounter.

Biff

 

[Roger Govier]

Hi Biff

Since the OP was recoring hours worked in a day, I somewhat "guessed" the
numerics would always be <=24 <bg>

 

[Biff]

It seems that everytime *I* assume or take something for
granted, it comes back to haunt me! <vbg>

Biff