Soma dos valores das linhas se os critérios forem encontrados na faixa horizontal

Question

Eu tenho uma planilha com vários registros de linha contendo country/value pares ( A2: C11 ) e registros de linha representando regions listando esses countries horizontalmente ( 14:16 ):

Oqueeugostariadefazerésomartodososvaloresdopaísparacadaregião:

Conceitualmente, sei o que preciso fazer, por exemplo, para a América do Norte ( B19 ):

• filtrar o intervalo B2: B11 em que B encontrado na linha 14
• some valores de C2: 11 correspondentes ao intervalo B2: B11 filtrado

Que neste caso daria 10 (C2) + 11 (C5) + 12 (C10) = 33

Acredito que as funções de que preciso são a pesquisa e funções de referência , mas eu não tenho ideia de por onde começar:

Alguém pode me dar um exemplo para o B19 (ou pelo menos uma lista de funções que eu deveria estar usando)?

PS: Eu não quero usar o VBA, apenas fórmulas

    

Answer 1

Experimente esta fórmula em B19 copiado para baixo

=SUMPRODUCT(SUMIF(B$2:B$11,INDEX(B$14:C$16,MATCH(A19,A$14:A$16,0),0),C$2:C$11))

• MATCH fornece a linha correta na tabela dois
• , em seguida, INDEX fornece todos os países nessa linha.
• A parte INDEX/MATCH , em seguida, forma o argumento de critérios para SUMIF e porque o critério é um intervalo de valores SUMIF retorna uma matriz (a soma de cada país separadamente) ...... so SUMPRODUCT é usado para somar o array, evitando "entrada de array"

Answer 2

Ok, como há mais de um país por região, você precisará de uma fórmula bastante longa. Estou sugerindo usar SUMIF() e VLOOKUP() .

=SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,2,FALSE),$C$2:$C$11)+
 SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,3,FALSE),$C$2:$C$11)

O primeiro SUMIF() calcula a soma apenas para o Canadá e a segunda apenas para os EUA. Adicionando os dois dá a soma para a região.

VLOOKUP() recupera o nome do país do nome da região, você perceberá que o diferente está no VLOOKUP() , em que o primeiro é 2 e o segundo é 3 (pouco antes do falso). É claro que você pode substituir FALSE by 0 para gerar os mesmos resultados.

Além disso, se você quiser uma fórmula mais curta, mas uma matriz, esta também funciona:

=SUM(SUMIF($B$2:$B$11,IF(A19=$A$14:$A$16,$B$14:$C$16),$C$2:$C$11))

Já que esta é uma fórmula de matriz, você terá que usar Ctrl + Deslocar + Enter

    

Answer 3

Você pode tentar isso:

=SUMPRODUCT($C$2:$C$11,--(IFERROR(MATCH($B$2:$B$11,$B14:$C14,0),0)<>0))

na célula B19 e arraste-a para baixo. Se você tiver mais de dois países por região, precisará estender o $B14:$C14 de acordo.

Observação: essa é uma fórmula de matriz, portanto, copie-a para a célula e pressione Ctrl + Shift + Enter para que ela funcione.

• IFERROR(MATCH(...,0)) retorna uma matriz cujos elementos mostram a posição de cada entrada da primeira tabela, na linha apropriada da segunda tabela.
• A condição lógica converte todos os zeros em FALSE e todos os outros números em TRUE
• A parte -- converte os valores TRUE e FALSE para 1 e 0 respectivamente
• SUMPRODUCT opera em dois arrays: o primeiro é o intervalo original e o segundo é um array 0/1 que engloba as condições que inserimos antes.

Aqui está uma boa postagem que explica a lógica por trás dessas fórmulas: link