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

Question

Estou tentando descobrir uma solução baseada em fórmulas 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.

Veja uma planilha de exemplo simplificada:

EuprecisocorresponderaletranacolunaF(A,B,C)àcoluna"Cartas" B e, em seguida, somar o valor na coluna "Valores" D uma vez por número distinto na coluna "Números" C e exibir essa soma na coluna G "Soma dos Valores".

A soma correta é exibida nas células da coluna G, mas eu 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 matrizes.

Se você puder obter uma matriz (lista) dos números em Valores (coluna D) onde Cartas (coluna B) é igual a "A" e Números tiver duplicatas removidas, você pode somar a matriz para obter a resposta.

Esta expressão:

(B$2:B$12=F2)

fornece uma matriz de valores True/False com True , sempre que a coluna B="A". Este aqui:

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

fornece uma matriz de valores True/False com True sempre que uma célula na coluna C não for igual à célula a seguir. Como suas duplicatas estão em células sequenciais (comentário abaixo, se esse não for sempre o caso), essa matriz tem False , em que as ocorrências extras de um valor são e, essencialmente, filtram as duplicatas. Multiplicando estas duas matrizes juntas:

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

converte valores de True/False para 1s e 0s e fornece uma matriz com 1 nas posições que queremos estar na soma. Usando essa matriz como logical_test em IF() e 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 dos valores na coluna D, onde havia um 1, intercalado com False onde quer que houvesse um zero. Agora podemos apenas somar o array. Esta fórmula, preenchida a partir do G2, apresenta os resultados abaixo.

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

Note que esta é uma fórmula de matriz e deve ser digitada com CTRL Deslocar Enter .

    

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 números. Eu usei a coluna A para esta fórmula, ou seja, a célula A2: = B2 & C2
2. Arraste esta fórmula para aplicar à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. Eu usei a coluna E para esta fórmula, ou seja, a célula E2: = COUNTIF (A $ 2: A2, A2)
4. Arraste esta fórmula para aplicar às células E2: E12
5. Use a seguinte fórmula em H2 para somar os valores correspondentes à letra, mas somente 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 aplicar às células G2: G4