CountifS + varios criterios + distinct count

Estoy buscando una fórmula de cálculo : distinct Count + varios criterios
Countifs () ¿pero no incluye distintas contar…

Aquí es un ejemplo.

Tengo una tabla en la que quiero contar el número de los distintos elementos (elemento de columna) la satisfacción de múltiples condiciones de una columna a y B : A>2 y B<5.

Descripción de la imagen aquí

CountifS + varios criterios + distinct count

Line  Item  ColA  ColB
1     QQQ    3     4
2     QQQ    3     3
3     QQQ    5     4
4     TTT    4     4
5     TTT    2     3
6     TTT    0     1
7     XXX    1     2
8     XXX    5     3
9     zzz    1     9

Countifs funciona de esta manera : COUNTIFS([ColumnA], los criterios A, [Columnab], los criterios B)

COUNTIFS([ColumnA], > 2 , [Columnab], < 5)

Devuelve : líneas 1,2,4,5,8 => Count = 5

¿Cómo puedo agregar una función distinct count basado en el Elemento de la Columna ? :

líneas 1,2, están en un único elemento QQQ

líneas de 4,5 son de un único elemento de TTT

La línea 8 es en un único elemento XXX

Devuelve Count = 3

¿Cómo puedo contar 3 ?!

Gracias

Usted puede descargar el archivo de excel @ Archivo de Excel

  • Parece que ha editado para añadir «zzz», por lo que su recuento final todavía debe ser de 3? o 4?
  • Sí, he añadido un ZZZ para hacer evidente que el resultado deseado (3) no es sólo el número de elementos, es el número de los distintos elementos de la satisfacción de las restricciones en las columnas a y B,
InformationsquelleAutor Vincent | 2016-06-23

2 Kommentare

  1. 4

    Feo fórmula, pero funciona.

    =SUM(((FREQUENCY(IF(C2:C10>2,1,0)*IF(D2:D10<5,1,0)*(COUNTIF(B2:B10,">"&B2:B10)+1),ROW(B2:B10)-ROW(B2)))*(ROW(B2:B11)-ROW(B2))>0)*1)
    

    Voy a empezar con los criterios de IFS:

    IF(C2:C10>2,1,0)*IF(D2:D10<5,1,0)
    

    Da una matriz de 1s y 0s para las filas que cumplen ambos criterios. MATRIZ = {1;1;1;1;0;0;0;1;0} por su ejemplo.

    Donde B2:B10 es el Elemento de la columna, la countif fórmula:

    COUNTIF(B2:B10,">"&B2:B10)
    

    devuelve {6;6;6;3;3;3;1;1;0} donde el número es igual al número de valores objeto de B2:B10 alfabéticamente menor que el probado valor del artículo.

    • QQQ va a 6 [3″TTT», 2″XXX», 1″zzz»]
    • TTT va a 3 [2″XXX», 1″zzz»]
    • XXX va a 1 [1″zzz»]
    • zzz llega a 0 [0 menos de «zzz»]

    Necesidad de añadir de 1 a esta matriz para asegurarse de que no hay valores 0:
    {7;7;7;4;4;4;2;2;1}.

    Así que cuando la multiplicación de los criterios, y la countif declaración:

    (IF(C2:C10>2,1,0)*IF(D2:D10<5,1,0)*(COUNTIF(B2:B10,">"&B2:B10)+1)
    

    Obtener la MATRIZ de = {7;7;7;4;0;0;0;2;0}.

    FREQUENCY(ARRAY,ROW(B2:B10)-ROW(B2))
    

    FILA(B2:B10)-FILA(B2) establece la frecuencia a los contenedores para {0;1;2;3;4;5;6;7;8}. Por lo tanto la salida de la frecuencia de la fórmula es {4;0;1;0;1;0;0;3;0;0} donde el último es 0 para todos los valores mayores que 8.

    ((FILA(B2:B11)-FILA(B2)>0)*1) es igual a {0;1;1;1;1;1;1;1;1;1}. Multiplicar la MATRIZ por esto quita el 0 recuento al inicio: MATRIZ de = {0;0;1;0;1;0;0;3;0;0}. [NOTA: B11 es menor elemento de celda de la columna+1 debido a la adición de la matriz de valor de la frecuencia de la fórmula para valores superiores a 8]

    (MATRIZ)>0)*1 = {0;0;1;0;1;0;0;1;0;0}

    SUMA este = 3.

    ctrl + mayús + entrar, porque es una fórmula de matriz.

    cmd + mayús + entrar para mac.

    • Esto es lo mejor que he visto en internet desde MSN messenger fue una cosa.
  2. 0

    Usted podría intentar esto:

    =SUMPRODUCT(1/COUNTIF(B2:B10,B2:B10))

    De crédito donde el crédito debido, sin embargo … he encontrado por aquí:

    https://exceljet.net/formula/count-unique-values-in-a-range-with-countif

    • No se corresponden con el tema. Lo que cuenta es el «Contar valores únicos en un rango», utilizando CountIF como una herramienta. Pero no es una Countif con distinct count.
    • Qué? Da los resultados que usted desea, utiliza CountIf … tratando de leer más en que el que está jugando con la semántica.

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea