Use INDIRECT ()
INDIRECT faz uma referência de intervalo válida a partir de uma string.
Então precisamos criar uma string. Podemos usar &
para concatenar a string:
"'" & A2 & "'!G:G"
Isso retornará:
'Sheet1'!G:G
'Sheet2'!G:G
'Sheet3'!G:G
À medida que é arrastado para baixo.
Então, só precisamos quebrar a fórmula acima em INDIRECT para retornar uma referência de intervalo real:
INDIRECT("'" & A2 & "'!G:G")
Isso agora retornará uma referência de intervalo válida, só precisamos adicioná-la ao COUNTIF:
=COUNTIF(INDIRECT("'" & A2 & "'!G:G"),"Yes")