formula needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
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
 
Hi Biff

Since the OP was recoring hours worked in a day, I somewhat "guessed" the
numerics would always be <=24 <bg>
 
It seems that everytime *I* assume or take something for
granted, it comes back to haunt me! <vbg>

Biff
 
Back
Top