Excel multiple IF AND OR Conditions

I am trying to make a status condition. Below is are the cells that I want to have a status with. I have 2 columns, Case Status and Bug Status

Sheet1 cell# Case Status Bug Status G8 Passed G9 Failed Fixed G10 Failed Pending G11 New Bug G12 New Bug Fixed

On the other (Sheet2) I want to know the general status of sheet1, if it is still "Open" or "Closed". My parameters would be, all "Failed" and "New Bug" case status should a corresponding "Fixed" status under Bug status column inorder to have a "Closed" status under sheet2 general status.

Any help will greatly appreciated. Thank you very much.