Adding amount of yes answers over multiple sheets

Hi folks,

I am currently trying to add the amount of yes answers given over roughly 20 sheets in the one workbook. I am using this formula
=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4,5,6,7,8}&"!D4"),"Yes")) which is working at the moment but once I change the name of any of the worksheets i get a #REF! in the cell.

Is it possible to change the name of the worksheets and still have this formula work.

Any help would be appreciated.

Thanks