Fórmula genérica
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Resumen
Para contar valores de texto únicos en un rango con criterios, puede usar una fórmula de matriz basada en las funciones FRECUENCIA y COINCIDIR. En el ejemplo que se muestra, la fórmula en G6 es:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
que devuelve 3, ya que tres personas diferentes trabajaron en el proyecto Omega.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
Ésta es una fórmula compleja que usa FRECUENCIA para contar valores numéricos que se derivan con la función COINCIDIR. Trabajando de adentro hacia afuera, la función COINCIDIR se usa para obtener la posición de cada valor que aparece en los datos:
MATCH(B5:B11,B5:B11,0)
El resultado de MATCH es una matriz como esta:
(1;1;3;1;1;6;7)
Como COINCIDIR siempre devuelve la posición de la primera coincidencia, los valores que aparecen más de una vez en los datos devuelven la misma posición. Por ejemplo, debido a que "Jim" aparece 4 veces en la lista, aparece en esta matriz 4 veces como el número 1.
Fuera de la función COINCIDIR, la función SI se usa para aplicar criterios, que en este caso implica probar si el proyecto es "omega" (de la celda G5):
IF(C5:C11=G5 // filter on "omega"
La función SI actúa como un filtro, permitiendo solo que los valores de MATCH pasen si están asociados con "omega". El resultado es una matriz como esta:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
La matriz filtrada se envía directamente a la función FREQUENCY como argumento data_array . A continuación, la función FILA se usa para construir una lista secuencial de números para cada valor en los datos:
ROW(B3:B12)-ROW(B3)+1
Esto crea una matriz como esta:
(1;2;3;4;5;6;7;8;9;10)
que se convierte en el argumento bins_array en FILTER. En este punto, tenemos:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY devuelve una matriz de números que indican un recuento para cada valor en la matriz de datos, organizado por bin. Cuando ya se ha contado un número, FREQUENCY devolverá cero. El resultado de FREQUENCY es una matriz como esta:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Nota: FREQUENCY siempre devuelve una matriz con un elemento más que bins_array .
En este punto, podemos reescribir la fórmula así:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Verificamos valores mayores que cero, lo que convierte los números en VERDADERO o FALSO:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Luego usamos un doble negativo para coaccionar los valores lógicos a 1 y 0:
=SUM((1;0;0;0;0;1;1;0))
Finalmente, la función SUM devuelve 3 como resultado final.
Nota: esta es una fórmula de matriz y debe ingresarse usando Control + Shift + Enter.
Manejo de celdas vacías en el rango
Si alguna celda del rango está vacía, deberá ajustar la fórmula para evitar que las celdas vacías pasen a la función COINCIDIR, lo que arrojará un error. Puede hacer esto agregando otra función SI anidada para verificar si hay celdas en blanco:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Con dos criterios
Si tiene dos criterios, puede ampliar la lógica de la fórmula agregando otro IF anidado:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Donde c1 = criterio1, c2 = criterio2 y vals = el rango de valores.
Con lógica booleana
Con la lógica booleana, puede reducir los IF anidados:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Esto hace que sea más fácil agregar y administrar criterios adicionales.
