Help Nested If and And

[madeleine]

Hi

I have terrible trouble reading and writing nested if and ANDs. I'd
really appreciate it if someone could help me here:

This is the formula I have written:

=IF($J9="PM",(IF(AND($K9<>"",$Q9<>"Yes"),SUM($C9:$E9))),0)

PM stands for the department, I have a range of them to check for, and
I need the calculation to be specific to the department.

K9 will have initials in it or nothing

Q9 will have Yes or No or nothing

C9-E9 will have numbers in them.

What I want to do is check if the department is the right department,
if it is then if there is something in the initials and Q9 does not
equate to yes I would like to sum C9 and E9 otherwise it should be
zero. However what happens is that if the department = "PM" then I
get False in the cell and a message saying there is an error in the
equation.

All help is welcome and appreciated.

Thanks

Madeleine

 

[Guest]

You need to provide a "FALSE" part for the 2nd IF:

=IF($J9="PM",
IF(AND($K9<>"",$Q9<>"Yes"),SUM($C9:$E9),FalsePart Needed
Here),0)

Therefore this should do what you need:

=IF($J9="PM",IF(AND($K9<>"",$Q9<>"Yes"),SUM($C9:$E9),0),0)

 

[Guest]

Try this:

=IF(AND($J9="PM",$K9<>"",$Q9<>"Yes"),SUM($C9:$E9),0)