Sumar valores de una columna según la coincidencia en otra columna y la primera aparición distinta del valor en una tercera columna

Question

Estoy tratando de encontrar una solución basada en fórmulas para sumar los valores de una columna basándose en la coincidencia de un valor en otra columna, pero solo para distintas apariciones de valores en una tercera columna.

Aquí hay una hoja de cálculo de muestra simplificada:

Necesito hacer coincidir la letra de la columna F (A, B, C) con la columna B de "Letras", y luego sumar el valor en la columna D de "Valores" una vez por número distinto en la columna C de "Números" y mostrar esa suma en columna G "Suma de Valores".

La suma correcta se muestra en las celdas de la columna G, pero no tengo una fórmula para lograrlo. ¡Cualquier ayuda sería apreciada!

Answer 1

Para este tipo de problema, es útil pensar en términos de matrices.

Si puede obtener una matriz (lista) de los números en Valores (columna D) donde Letras (columna B) es igual a "A" y Números tiene duplicados eliminados, puede simplemente sumar la matriz para obtener la respuesta.

Esta expresión:

(B$2:B$12=F2)

da una matriz de True/Falsevalores Truedonde la columna B = "A". Éste:

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

proporciona una matriz de True/Falsevalores Truedonde una celda de la columna C no es igual a la siguiente celda. Debido a que sus duplicados están en celdas secuenciales (comente a continuación si este no será siempre el caso), esta matriz tiene Falsedónde están las apariciones adicionales de un valor y, esencialmente, filtra los duplicados. Multiplicando estas dos matrices juntas:

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

convierte True/Falsevalores a 1 y 0, y proporciona una matriz con 1 en las posiciones que queremos que estén en la suma. Usando esta matriz como logical_testen an IF()y la columna D como value_if_true:

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

devuelve una matriz de los valores en la columna D donde haya un 1, intercalados Falsedonde haya un cero. Ahora podemos simplemente sumar la matriz. Esta fórmula, completada desde G2, proporciona los resultados que se muestran a continuación.

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

Tenga en cuenta que esta es una fórmula matricial y debe ingresarse con CTRLShiftEnter.

Answer 2

Si puede agregar columnas a sus datos, lo siguiente funcionará para el ejemplo simple dado:

1. Agrega una fórmula para concatenar las letras y los números. He utilizado la columna A para esta fórmula, es decir, celda A2: =B2&C2
2. Arrastre esta fórmula hacia abajo para aplicarla a las celdas A2:A12
3. Agregue una fórmula para probar si cada concatenación de letras y números es la primera concatenación única. He utilizado la columna E para esta fórmula, es decir, la celda E2: =CONTAR.SI(A$2:A2,A2)
4. Arrastre esta fórmula hacia abajo para aplicarla a las celdas E2:E12
5. Utilice la siguiente fórmula en H2 para sumar los valores donde coincide la letra, pero solo para la primera concatenación de letra y número, es decir, celda G2: =SUMIFS($D$2:$D$12,$B$2:$B$12,F2 ,$E$2:$E$12,1)
6. Arrastre esta fórmula hacia abajo para aplicarla a las celdas G2:G4