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.

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.

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.

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

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

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 -

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 -

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).

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).