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 |
#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 | |
|
|