IF, AND & OR

[Andy Roberts]

I have a list of employees which includes their department and employment
status i.e.

Emp1 Dept1 Employed
Emp2 Dept1 Employed
Emp3 Dept2 Employed
Emp4 Dept3 Employed
Emp5 Dept1 Left

What I want to do in the D column is run a calculation based on meeting
either 1 of 2 conditions in column B and 1 condition in column C. I want to
be able to calculate the number of hours worked for each employee that is
either in Dept1 or Dept2 and is also employed.

At the moment I have (for cell D1)

=IF(AND(B1="Dept1",C1="Employed"),SUM(K$4*$G5)*$H5,0)

Columns H, K & G just hold numbers which aren't the problem as this part of
the equation works - in fact the above equation does work but I want it to
meet EITHER Dept1 OR Dept2 in column B as well as the condition in column C

In plain speak I want the calculation to return an answer if the employee is
in department 1 or 2 and is currently employed otherwise return 0. I cant
work out how to add the OR statement in.

Hope this makes sense

Andy
Office 2010
Win XP Pro

 

[Claus Busch]

Hi Andy,

Am Wed, 13 Feb 2013 16:08:11 -0000 schrieb Andy Roberts:

I have a list of employees which includes their department and employment
status i.e.

Emp1 Dept1 Employed
Emp2 Dept1 Employed
Emp3 Dept2 Employed
Emp4 Dept3 Employed
Emp5 Dept1 Left

What I want to do in the D column is run a calculation based on meeting
either 1 of 2 conditions in column B and 1 condition in column C. I want to
be able to calculate the number of hours worked for each employee that is
either in Dept1 or Dept2 and is also employed.

try:
=IF(AND(OR(B1="Dept1",B1="Dept2"),C1="Employed"),K$4*$G5*$H5,0)


Regards
Claus Busch

 

[Claus Busch]

Hi Andy,

Am Wed, 13 Feb 2013 17:17:46 +0100 schrieb Claus Busch:

=IF(AND(OR(B1="Dept1",B1="Dept2"),C1="Employed"),K$4*$G5*$H5,0)

a little bit shorter and without IF:
=(OR(B1={"Dept1","Dept2"}))*(C1="Employed")*K$4*$G5*$H5


Regards
Claus Busch

 

[Andy Roberts]

Perfect Claus

Many Thanks

Hi Andy,

Am Wed, 13 Feb 2013 17:17:46 +0100 schrieb Claus Busch:


a little bit shorter and without IF:
=(OR(B1={"Dept1","Dept2"}))*(C1="Employed")*K$4*$G5*$H5


Regards
Claus Busch