A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

formula needed



 
 
Thread Tools Display Modes
  #1  
Old January 28th, 2004, 02:39 AM
external usenet poster
 
Posts: n/a
Default formula needed

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
Ads
  #2  
Old January 28th, 2004, 03:03 AM
Roger Govier
external usenet poster
 
Posts: n/a
Default formula needed

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.

--
Regards
Roger Govier
wrote in message
...
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



  #3  
Old January 28th, 2004, 04:01 AM
Biff
external usenet poster
 
Posts: n/a
Default formula needed

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

-----Original Message-----
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.

--
Regards
Roger Govier
wrote in message
...
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



.

  #4  
Old January 28th, 2004, 04:10 AM
Biff
external usenet poster
 
Posts: n/a
Default formula needed

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

-----Original Message-----
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

-----Original Message-----
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.

--
Regards
Roger Govier
wrote in message
...
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



.

.

  #5  
Old January 28th, 2004, 10:13 AM
Roger Govier
external usenet poster
 
Posts: n/a
Default formula needed

Hi Biff

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

--
Regards
Roger Govier
"Biff" wrote in message
...
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

-----Original Message-----
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

-----Original Message-----
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.

--
Regards
Roger Govier
wrote in message
...
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


.

.



  #6  
Old January 28th, 2004, 10:22 PM
Biff
external usenet poster
 
Posts: n/a
Default formula needed

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

Biff

-----Original Message-----
Hi Biff

Since the OP was recoring hours worked in a day, I

somewhat "guessed" the
numerics would always be =24 bg

--
Regards
Roger Govier
"Biff" wrote in message
...
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

-----Original Message-----
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

-----Original Message-----
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.

--
Regards
Roger Govier
wrote in message
...
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


.

.



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.