Fórmula genérica
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Resumo
Para somar condicionalmente intervalos idênticos que existem em planilhas separadas, tudo em uma fórmula, você pode usar a função SOMASE com INDIRETO, agrupado em SUMPRODUCT. No exemplo mostrado, a fórmula em C9 é:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Explicação
Os dados em cada uma das três planilhas sendo processadas são assim:
Em primeiro lugar, observe que você não pode usar SUMIFs com uma referência 3D "normal" como esta:
Sheet1:Sheet3!D4:D5
Esta é a "sintaxe 3D" padrão, mas se você tentar usá-la com SUMIF, receberá um erro #VALUE. Portanto, para contornar esse problema, você pode usar um intervalo nomeado "planilhas" que lista cada planilha (guia da planilha) que deseja incluir. No entanto, para construir referências que o Excel interpretará corretamente, precisamos concatenar os nomes das planilhas aos intervalos com os quais precisamos trabalhar e, em seguida, usar o INDIRETO para fazer o Excel reconhecê-los corretamente.
Além disso, como o intervalo nomeado "planilhas" contém vários valores (ou seja, é um array), o resultado de SUMIF, neste caso, também é um array (às vezes chamado de "array resultante). Portanto, usamos SUMPRODUCT para tratá-lo, pois SUMPRODUCT tem a capacidade de manipular matrizes nativamente sem exigir Ctrl-Shift-Enter, como muitas outras fórmulas de matriz.
Outra maneira
O exemplo acima é um tanto complicado. Outra maneira de lidar com esse problema é fazer uma soma condicional "local" em cada folha e, em seguida, usar uma soma 3D normal para somar cada valor na guia de resumo.
Para fazer isso, adicione uma fórmula SOMASE a cada planilha que usa uma célula de critérios na planilha de resumo. Então, quando você alterar os critérios, todas as fórmulas SOMASE vinculadas serão atualizadas.
