A função SUMIF é feita para este propósito. Veja a ajuda da função do Excel para mais detalhes sobre os 3 parâmetros:
SUMIF(range, criteria, [sum_range])
Folha2! C: C = intervalo (quais são os seus critérios)
Folha1! D3 = critérios (quais devem ser os valores de seu intervalo)
Folha2! Z: Z = sum_range (o que você está adicionando)
=SUMIF(Sheet2!C:C,Sheet1!D3,Sheet2!Z:Z)
Note, suponho que você queira dizer Coluna C na sua pergunta, e não na Linha C. Se não, por favor, reescreva.