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. 


Thread Tools  Display Modes 
#1




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




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




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




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




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




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  

