Re: Conditional expression problem

drdr32 wrote on 2005/06/21 08:26:

> I have a work*** that has sign-in data for people working one of 5
> shifts every day - I download information from a database and paste it
> into the *** and it calculates hours worked for each shift, puts them
> into columns for each person, and sums hours for different shifts that
> are paid at different rates. Now I need to identify weekends, so I
> added a column that uses the WEEKDAY function to resolve to true or
> false for each shift.
>
> Now I'm trying to modify the expression that sums the hours worked on a
> particular shift called "RMP Day" which also happen to be weekends. In
> the example shown, the column E has the shift name, H has the hours
> data (to be summed), and V has the TRUE/FALSE for weekends. It doesn't
> work - I get 0 whether I try to sum based on true or false. Any ideas?
>
> Thanks!
>
> GR
>
> {=SUM(IF(AND($E$7:$E$207="RMP Day",$V$7:$V$207=FALSE),H$7:H$207))}
>

For some reason that others may be able to explain, AND seems to be the
problem in an array formula. Rewrite your formula this way:

{=SUM(IF($E$7:$E$207="RMP Day",IF($V$7:$V$207=FALSE,$H$7:$H$207)))}

If RMP Days are always on weekend, why do you test for weekend?

JL
Mac OS X 10.3.9, Office v.X 10.1.6

.