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 Help
I have a worksheet/form which is being used to track time spent on specialty
details in my office. The user needs to indicate which days of the month are work days and will then have to show how much time they are spending away from their normal job for these specialty assignments. Many people with different work schedules will be using this form. I want to come up with a formula that can add hours in a column where the work days are denoted by a W. Non work days will be blank. Example: W W W (Indicates work days) 1 2 3 4 5 6 7 (Days of Month) 5 2 1 2 (Hours worked per day of the week) In this example, I am only interested in computing the hours showing under the W column. In another calculation, I would also like to compute the hours which show up in the column without the W. |
#2
|
|||
|
|||
Formula Help
Try this:
=SUMIF(1:1,"W",4:4) I've assumed your W's are in row 1 and your hours worked are in row 4. Hope this helps. Pete On Aug 27, 2:41*pm, Lee Ann Lee wrote: I have a worksheet/form which is being used to track time spent on specialty details in my office. *The user needs to indicate which days of the month are work days and will then have to show how much time they are spending away from their normal job for these specialty assignments. *Many people with different work schedules will be using this form. *I want to come up with a formula that can add hours in a column where the work days are denoted by a W. *Non work days will be blank. Example: W * * * *W * *W * * (Indicates work days) 1 *2 *3 *4 *5 *6 *7 (Days of Month) 5 *2 *1 * * *2 * * * * (Hours worked per day of the week) In this example, I am only interested in computing the hours showing under the W column. *In another calculation, I would also like to compute the hours which show up in the column without the W. |
#3
|
|||
|
|||
Formula Help
Worked like a champ - thanks so much. How would I capture the totals in the
colomns which contain no W. "Pete_UK" wrote: Try this: =SUMIF(1:1,"W",4:4) I've assumed your W's are in row 1 and your hours worked are in row 4. Hope this helps. Pete On Aug 27, 2:41 pm, Lee Ann Lee wrote: I have a worksheet/form which is being used to track time spent on specialty details in my office. The user needs to indicate which days of the month are work days and will then have to show how much time they are spending away from their normal job for these specialty assignments. Many people with different work schedules will be using this form. I want to come up with a formula that can add hours in a column where the work days are denoted by a W. Non work days will be blank. Example: W W W (Indicates work days) 1 2 3 4 5 6 7 (Days of Month) 5 2 1 2 (Hours worked per day of the week) In this example, I am only interested in computing the hours showing under the W column. In another calculation, I would also like to compute the hours which show up in the column without the W. |
#4
|
|||
|
|||
Formula Help
Try: =SUMIF(1:1,"W",4:4)
-- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "Lee Ann" wrote: .. How would I capture the totals in the columns which contain no W |
#5
|
|||
|
|||
Formula Help
Thank you for the response. The poster prior to you offered a very similar
formula (without the ) and it worked. I appreciate your help!! "Max" wrote: Try: =SUMIF(1:1,"W",4:4) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "Lee Ann" wrote: .. How would I capture the totals in the columns which contain no W |
#6
|
|||
|
|||
Formula Help
Thanks for feeding back, Lee Ann. My formula counts where row 1 =
"W" (the equals sign is not needed), whereas Max's formula counts for not equal to "W". For some reason those operators have to be expressed within quotes. Pete On Aug 27, 3:58*pm, Lee Ann wrote: Thank you for the response. *The poster prior to you offered a very similar formula (without the ) and it worked. *I appreciate your help!! "Max" wrote: Try: =SUMIF(1:1,"W",4:4) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "Lee Ann" wrote: .. How would I capture the totals in the columns which contain no W- Hide quoted text - - Show quoted text - |
#7
|
|||
|
|||
Formula Help
Okay, I see what he was writing. That will take care of the Non W columns.
I tried using a 0, in lieu of the "W" and that seemed to pick up the totals in the non W columns. For some reason, however it would not read the same rows as the formula to calculate the Ws. This other one works, though. Thank you both for your responses. Such a simple formula to save alot of folks some major headaches (and mathematical errors). "Pete_UK" wrote: Thanks for feeding back, Lee Ann. My formula counts where row 1 = "W" (the equals sign is not needed), whereas Max's formula counts for not equal to "W". For some reason those operators have to be expressed within quotes. Pete On Aug 27, 3:58 pm, Lee Ann wrote: Thank you for the response. The poster prior to you offered a very similar formula (without the ) and it worked. I appreciate your help!! "Max" wrote: Try: =SUMIF(1:1,"W",4:4) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "Lee Ann" wrote: .. How would I capture the totals in the columns which contain no W- Hide quoted text - - Show quoted text - |
#8
|
|||
|
|||
Formula Help
You're welcome. If you want to sum where the cells in row 1 are empty
(same as "not W" in this case), you would use: =SUMIF(1:1,"",4:4) Pete On Aug 27, 4:19*pm, Lee Ann wrote: Okay, I see what he was writing. *That will take care of the Non W columns. * I tried using a 0, in lieu of the "W" and that seemed to pick up the totals in the non W columns. *For some reason, however it would not read the same rows as the formula to calculate the Ws. *This other one works, though. Thank you both for your responses. *Such a simple formula to save alot of folks some major headaches (and mathematical errors). |
#9
|
|||
|
|||
Formula Help
No, ignore that last comment - I need some caffeine !! bg
Pete "Pete_UK" wrote in message ... You're welcome. If you want to sum where the cells in row 1 are empty (same as "not W" in this case), you would use: =SUMIF(1:1,"",4:4) Pete On Aug 27, 4:19 pm, Lee Ann wrote: Okay, I see what he was writing. That will take care of the Non W columns. I tried using a 0, in lieu of the "W" and that seemed to pick up the totals in the non W columns. For some reason, however it would not read the same rows as the formula to calculate the Ws. This other one works, though. Thank you both for your responses. Such a simple formula to save alot of folks some major headaches (and mathematical errors). |
Thread Tools | |
Display Modes | |
|
|