IF AND formula/function problem

[mickjjuk]

Can anyone help me as I have been going around in circles trying to solve this problem!

I have a cell in worksheet1 (Cell ref S7) which is either blank or has a "Y" input

On worksheet 2 in cell ref AO7 I need a function that will firstly check if worksheet1 cell has a "Y" if not then return 0, if "Y" then check if worksheet2 cells J7 AND K7 is > 0 , if both are > 0 return a 0. If J7 is > 0 and K7 = 0 then return value in cell AO2, if J7 = 0and K7 > 0 then return value in cell AO2

Hope this explains what I mean.

Thanks

[Fotis1991]

Try

=IF(Sheet1!S7<>"Y",0,IF(AND(Sheet2!J7>0,Sheet2!K7>0),0,IF(AND(Sheet2!J7>0,Sheet2!K7=0),Sheet2!AO2,IF(AND(Sheet2!J7=0,Sheet2!K7>0),Sheet2!AO2))))

[K m]

Assume S7 is the cell to be checked:

=IF(Sheet1!S7<>"Y",0,IF(AND(Sheet2!J7>0,Sheet2!K7>0),0,IF(OR(AND(Sheet2!J7>0,Sheet2!K7=0),(AND(Sheet2!J7=0,Sheet2!K7>0))),AO2,"")))

You do notice that you have two tests with the same result
If J7 is > 0 and K7 = 0 then return value in cell AO2, if J7 = 0 and K7 > 0 then return value in cell AO2

[mickjjuk]

Many thanks for the answers guys. I can now see where I was going wrong.