Somar valores de uma coluna com base na correspondência em outra coluna e na primeira ocorrência distinta de valor em uma terceira coluna

Question

Estou tentando descobrir uma solução baseada em fórmula para somar os valores em uma coluna com base na correspondência de um valor em outra coluna, mas apenas para ocorrências distintas de valores em uma terceira coluna.

Aqui está um exemplo de planilha simplificada:

Preciso combinar a letra na coluna F (A, B, C) com a coluna "Letras" B e, em seguida, somar o valor na coluna "Valores" D uma vez por número distinto na coluna C "Números" e exibir essa soma em coluna G "Soma dos Valores".

A soma correta é exibida nas células da coluna G, mas não tenho uma fórmula para fazer isso. Qualquer ajuda seria apreciada!

Answer 1

Para esse tipo de problema, ajuda pensar em termos de arrays.

Se você conseguir uma matriz (lista) dos números em Valores (coluna D) onde Letras (coluna B) são iguais a "A" e Números tem duplicatas removidas, você pode simplesmente somar a matriz para obter a resposta.

Esta expressão:

(B$2:B$12=F2)

fornece uma matriz de True/Falsevalores onde Truequer que a coluna B = "A". Este:

(C$2:C$12<>C$3:C$13)

fornece uma matriz de True/Falsevalores Trueonde quer que uma célula na coluna C não seja igual à célula seguinte. Como suas duplicatas estão em células sequenciais (comente abaixo se nem sempre será o caso), esse array tem Falseonde estão as ocorrências extras de um valor e, essencialmente, filtra as duplicatas. Multiplicando essas duas matrizes:

(B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)

converte True/Falsevalores em 1 e 0 e fornece um array com 1 nas posições que queremos que esteja na soma. Usando esta matriz como logical_testem an IF()e a coluna D como value_if_true:

IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12)

retorna uma matriz de valores na coluna D onde quer que houvesse 1, intercalados com Falseonde quer que houvesse zero. Agora podemos apenas somar o array. Esta fórmula, preenchida a partir de G2, dá os resultados mostrados abaixo.

=SUM(IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12))

Observe que esta é uma fórmula de matriz e deve ser inserida com CTRLShiftEnter.

Answer 2

Se você conseguir adicionar colunas aos seus dados, o seguinte funcionará para o exemplo simples fornecido:

1. Adicione uma fórmula para concatenar as letras e os números. Usei a coluna A para esta fórmula, ou seja, Célula A2: =B2&C2
2. Arraste esta fórmula para baixo para aplicá-la às células A2:A12
3. Adicione uma fórmula para testar se cada concatenação de letras e números é a primeira concatenação exclusiva. Usei a coluna E para esta fórmula, ou seja, Célula E2: =COUNTIF(A$2:A2,A2)
4. Arraste esta fórmula para baixo para aplicá-la às células E2:E12
5. Use a seguinte fórmula em H2 para somar os valores onde a letra corresponde, mas apenas para a primeira concatenação de letra e número, ou seja, Célula G2: =SUMIFS($D$2:$D$12,$B$2:$B$12,F2 ,$E$2:$E$12,1)
6. Arraste esta fórmula para baixo para aplicá-la às células G2:G4