No Excel, como posso somar valores em um intervalo se o nome correspondente corresponder a um nome em uma tabela diferente?

Question

Como o título provavelmente não é tão claro, deixe-me explicar meu problema. Eu tenho um conjunto de dados com valores correspondentes aos nomes:

Tabela 1

name      amount
adrian    1
alice     2
austin    3
bob       4
bruce     5
clarice   6
cathy     7
charles   8
adrian    9
alice     10
austin    11
bob       12
bruce     13
clarice   14
cathy     15
charles   16

Esses nomes são combinados com outro nome em uma tabela (para este exemplo, usei a primeira letra de seus nomes, o problema real é bem diferente).

Tabela 2

name1  name2
a      adrian
a      alice
a      austin
b      bob
b      bruce
c      clarice
c      cathy
c      charles

Eu tenho uma terceira tabela, onde gostaria de somar todos os valores da Tabela 1, onde o nome corresponde ao valor correto na terceira tabela:

Tabela 3

name    amount
a       this should be 36 (the sum of arian, alice and austin)
b       this should be 34 (the sum of bob and bruce)
c       this should be 66 (the sum of clarice, cathy and charles)

Eu tentei usar a seguinte função na Tabela 3, mas isso sempre resulta em 0. Alguém pode me ajudar a identificar o que estou fazendo errado?

=SUMIF(INDEX(Table1;;2);INDEX( <range name 1> ;MATCH(INDEX(Table1;;1); <range name 2> ;0)); <letter in table 3>)

    

Answer 1

Isso será iterado na segunda tabela e, se o Name1 corresponder, retornará o valor SUMIF () de Name2 da primeira tabela e os adicionará a todos:

=SUMPRODUCT(SUMIF(A:A,$F$2:$F$9,B:B)*($E$2:$E$9=H2))

Observe que as referências à Tabela 2 estão limitadas aos dados reais, enquanto as referências à tabela 1 podem ser referências de colunas completas. Isso tem a ver com como a fórmula irá interagir. Como literalmente iterará pela segunda tabela, queremos limitar o número de iterações ao próprio conjunto de dados. Embora a fórmula SUMIF () já esteja otimizada e não tenha prejudicado as referências completas da coluna.