Working out completion % for renovation in my spreadsheet

I basically want to make some graphs for my spread sheet showing completion % for each area of the house im renovating.

I've got 2 columes i wish to get data from, one is for the room im renovating and the other is for Y or N if a job has been completed.

I've tried using =COUNTIF(Sheet1!D2:D500,"Y"+Sheet1!E2:E500,"HallwayGroundFloor")/COUNTA(Sheet1!D2:D500) but it doesnt work. I think its because it has too many countif's in.

I used this to =COUNTIF(SHEET1!D2:D500,"Y")/COUNTA(SHEET1!D2:D500) for getting the % of yes's overall but that doesnt sort by room just overall.

Any help would be greatly appreciated.