Conditional expression problem

[drdr32]

I have a worksheet 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 sheet 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))}

 

[J Laroche]

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

I have a worksheet 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 sheet 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

 

[JE McGimpsey]

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

The AND() function returns a single value, rather than an array (even in
an array formula), so the result will be whatever the AND of E7="RMP
Day" and V7=FALSE.

You can do this as a non-array function with

=SUMPRODUCT(--($E$7:$E$207="RMP Day"),--($V$7:$V$207=FALSE),$H$7:$H207)

for an explanation of the "--", see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Bob Greenblatt]

I have a worksheet 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 sheet 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))}

In addition to the other suggestions offered, I usually just multiply them
all together. Remember that true resolves to 1 and false to zero.

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

 

[drdr32]

To all: THANKS! the modification suggested by JL worked great. This is
a great place to save time trying to sort out problems.

JL - RMP Days are NOT always on weekends - hence the issue. Thanks
again!

Geoff