Formula Help

[Lee Ann]

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.

 

[mikerickson]

It looks like SUMIF will do what you want.

=SUMIF(A1:E1,"W",A2:E2) will return the M situation.
=SUMIF(A1:E1,"<>W",A2:E2) returns "not M"

 

[Pete_UK]

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

 

[Lee Ann]

Worked like a champ - thanks so much. How would I capture the totals in the
colomns which contain no W.

 

[Max]

Try: =SUMIF(1:1,"<>W",4:4)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Lee Ann]

Thank you for the response. The poster prior to you offered a very similar
formula (without the <>) and it worked. I appreciate your help!!

 

[Pete_UK]

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

 

[Lee Ann]

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]

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

 

[Pete_UK]

No, ignore that last comment - I need some caffeine !! <bg>

Pete

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