☰

Fórmula de matriz de fecha Countif

tag-icon • tag-icon microsoft-excel conditional-formatting array countif
Fórmula de matriz de fecha Countif

Estoy intentando crear una hoja para realizar un seguimiento del cumplimiento de la formación. Necesito crear algunos campos de resumen que muestren cuántos procedimientos está al día una persona, cuántos están desactualizados y a cuántos se les ha emitido un nuevo procedimiento desde que fueron capacitados.

Entonces, para los datos que se muestran a continuación, necesito:

  • un cálculo que analiza las tres fechas al lado de la persona A y calcula cuántas de ellas son posteriores a la Fecha 1 y a la Fecha 2 que están encima de ellas.
  • un cálculo que analiza las tres fechas al lado de la persona A y calcula cuántas de ellas están entre la Fecha 1 y la Fecha 2 encima de ellas
  • un cálculo que analiza las tres fechas al lado de la persona A y calcula cuántas de ellas son anteriores a la Fecha 1 y la Fecha 2 que están encima de ellas.

He estado probando una fórmula matricial usando lo siguiente, pero no parece funcionar como esperaba. Solo funciona si la fecha al lado de la persona A es mayor que todas las fechas al lado de la Fecha 1 y la Fecha 2, no solo en la columna de arriba.

=COUNTIFS($G65:$GX65,">="&$G$10:$GX$10,$G65:$GX65,">="&$G$6:$GX$6)

También intenté agregar un carácter a la fecha junto a la persona A usando formato condicional, pero la función CONTAR.SI no encuentra ese carácter si se agrega mediante formato.

¿Algun consejo?

ingrese la descripción de la imagen aquí

Respuesta1

Si se da el caso de que, como en tu ejemplo, Date 1siempre es posterior a Date 2, puedes utilizar estas fórmulas:

  Later:  =SUMPRODUCT(--($E6:$G6>$E$2:$G$2))
Between:  =SUMPRODUCT(($E6:$G6<=$E$2:$G$2)*($E$6:$G$6>=$E$3:$G$3))
Earlier:  =SUMPRODUCT(--($E$6:$G$6<$E$3:$G$3))

ingrese la descripción de la imagen aquí

Si la fecha 1 esNOsiempre la fecha posterior, las fórmulas solo necesitarían ampliarse un poco para comparar la fecha adecuada

Respuesta2

Dado que OP mencionó calcular, tres fechas al lado de Person Ay calcula cuántas de ellas son later, between and earlierque ambas Date 1y Date 2encima de ellas

Por lo tanto, he usado COUNTIF& COUNTIFSpara comparar fechas G69:I69con fecha 1& 2en G65:I66.

ingrese la descripción de la imagen aquí

  • Fórmula de matriz (CSE) en la celda D69:

    {=COUNTIF($G$69:$I$69,">"&$G$65:$I$66)}

Las fechas son 01-Jan-20& 15-Oct-19.

  • Fórmula de matriz (CSE) en la celda E69:

    {=COUNTIF($G$69:$I$69,"<"&$G$65:$I$66)}

Las fechas son 01-Sep-19.

  • Fórmula de matriz (CSE) en la celda F69:

    {=COUNTIFS($G$69:$I$69,">="&MIN($G$65:$I$66),$G$69:$I$69,"<="&MAX($G$65:$I$66))}

Las fechas son 01-sep-19& 15-Oct-19.

NÓTESE BIEN

  • Termine la fórmula D69, E69 & F69conCtrl+Mayús+Entrar.

  • Ajuste las referencias de celda en la fórmula según sea necesario.

información relacionada