¿Cómo contar el número de ocurrencias por año/trimestre/mes/semana en Excel?

Cuando necesitas averiguar rápidamente cuántas veces ocurrió un cierto evento en un año o mes particular, las fórmulas ofrecen un enfoque flexible y dinámico. Al usar funciones de fecha integradas junto con SUMAPRODUCTO, puedes calcular directamente los recuentos por año, mes o cualquier combinación de ellos, haciendo que tu resumen sea preciso y se actualice automáticamente a medida que cambian los datos de origen. Este enfoque funciona bien en la mayoría de las tareas de análisis rutinarias para conjuntos de datos pequeños a medianos.

Selecciona una celda en blanco donde deseas mostrar el resultado del recuento, luego ingresa la siguiente fórmula:

=SUMAPRODUCTO((MES($A$2:$A$24)=F2)*(AÑO($A$2:$A$24)=$E$2))

Después de ingresar la fórmula, arrastra el controlador de AutoRelleno de la celda hacia abajo para aplicar la fórmula a otras filas según sea necesario. Como se muestra a continuación:

Notas y consejos:

• En la fórmula MES($A$2:$A$24)=F2 y AÑO($A$2:$A$24)=$E$2 son criterios que coinciden con el mes especificado en F2 y el año en E2. Actualiza los rangos y referencias (como A2:A24, E2, F2) para adaptarlos a la disposición de tus datos.
• Para un recuento solo por mes, ignorando el año, usa:
  =SUMAPRODUCTO(1*(MES($A$2:$A$24)=F2))
• Asegúrate de que la columna de fecha contenga valores de fecha reales de Excel, no fechas formateadas como texto para evitar errores o discrepancias. Si tu fórmula devuelve resultados inesperados, verifica nuevamente el formato de fecha.
• Si tu conjunto de datos es grande, considera usar Tablas dinámicas o VBA para obtener mejor rendimiento y mantenimiento más fácil.

Este método es adecuado para la mayoría de los escenarios que requieren estadísticas rápidas de fechas y cuando deseas que los resultados se actualicen automáticamente al modificar los datos. Sin embargo, trabajar con múltiples condiciones de agrupamiento puede hacer que las fórmulas sean complejas y difíciles de mantener.

Si tienes instalado Kutools para Excel, puedes aprovechar sus utilidades intuitivas para agrupar y contar el número de ocurrencias por año, mes, día de la semana, día o combinaciones adicionales como año y mes o mes y día, sin necesidad de construir fórmulas complejas. Este enfoque es especialmente eficiente para usuarios que buscan una solución visual y guiada por menús.

1. Selecciona la columna que contiene tus fechas y haz clic en Kutools > Formato > Aplicar formato de fecha. Aparecerá el siguiente cuadro de diálogo:

2. En el cuadro de diálogo Aplicar formato de fecha, selecciona el estilo de formato correspondiente a tu requisito de conteo (como mes, año, día de la semana, día, etc.), y luego haz clic en Aceptar. Por ejemplo, selecciona "Mar" para contar por mes.

3. Mientras la columna de fecha aún está seleccionada, haz clic en Kutools > A Reales. Este paso convierte todas las fechas a los valores mostrados (por ejemplo, nombres de meses) para facilitar la agrupación en pasos posteriores.

4. Luego, selecciona el rango que contiene tus nombres de grupo convertidos y datos asociados (como columnas de Cantidad o Categoría). Ve a Kutools > Texto > Combinar filas avanzado. Verás la siguiente interfaz:

5. En el cuadro de diálogo Combinar filas avanzado:
(1) Establece tu columna de fecha como la Clave principal para agrupar por ella.
(2) Para la columna que deseas contar (por ejemplo, Cantidad), establece el cálculo en Contar.
(3) Puedes elegir otros métodos de agregación o combinación para otras columnas (por ejemplo, combinar nombres de frutas con una coma).
(4) Haz clic en Aceptar para procesar.

Ahora tus datos mostrarán el recuento de registros por período seleccionado. Ver captura de pantalla a continuación:

En comparación con fórmulas manuales, Kutools simplifica el proceso, reduce errores humanos y es altamente adecuado para usuarios que realizan con frecuencia conteos agrupados y desean evitar la complejidad de las fórmulas. Funciona bien tanto para conjuntos de datos pequeños como grandes. Recuerda hacer una copia de seguridad de tus datos antes de convertir o combinar filas en masa.

Las tablas dinámicas ofrecen una forma poderosa e interactiva de analizar grandes conjuntos de datos y resumir ocurrencias por una o más dimensiones de tiempo —año, mes, trimestre, hora, etc.— todo con una interfaz de clic fácil. Las tablas dinámicas también permiten una rápida reconfiguración y filtrado, lo que las hace ideales para explorar patrones de datos o preparar informes de gestión.

1. Selecciona tu tabla de datos, luego ve a Insertar > Tabla dinámica. Aparecerá el cuadro de diálogo Crear tabla dinámica.

2. En el cuadro de diálogo, especifica dónde colocar la tabla dinámica (hoja nueva o una ubicación existente como la celda E1), luego haz clic en Aceptar.

3. En el panel Campos de la tabla dinámica, arrastra el Campo Fecha a la sección Filas y el Cantidad (o campo objetivo) a la sección Valores. Por defecto, los valores pueden ser sumados.

La tabla dinámica aparece como en la captura de pantalla a continuación:

4. Cambia el cálculo de valor a un recuento haciendo clic derecho en el título de la columna de valores (por ejemplo, Suma de Cantidad), luego seleccionando Resumir valores por > Contar.

5. Para agrupar por períodos adicionales (como mes, año o trimestre), haz clic derecho en cualquier celda en la columna Etiquetas de fila, selecciona Agrupar, y desde el cuadro de diálogo, elige los criterios de agrupamiento (como Meses, Años o Trimestres), luego haz clic en Aceptar.

Tu tabla ahora muestra recuentos por los períodos seleccionados:

Nota: Agrupar por múltiples períodos (por ejemplo, mes y año) agregará niveles adicionales en las Etiquetas de fila. Puedes reorganizar campos de agrupamiento (por ejemplo, mover Años debajo de Fecha) en el panel Campos de la tabla dinámica para ajustar tu vista de resumen.

Este enfoque es el mejor para conjuntos de datos grandes y dinámicos, que requieren agrupamiento periódico, comparación y resumen. Es menos adecuado para cálculos rápidos a nivel de celda o para usuarios que no están familiarizados con las características de las tablas dinámicas.

Cuando necesitas generar repetidamente resúmenes de ocurrencias agrupadas por varios períodos de tiempo, o deseas automatizar el proceso de conteo para mayor eficiencia—especialmente en grandes conjuntos de datos—una macro VBA personalizada puede ser una solución efectiva. Este método es altamente adecuado si procesas datos regularmente, produces tablas de resumen periódicas o necesitas agrupamientos personalizados (como trimestres fiscales o semanas) que no se manejan fácilmente con fórmulas o tablas dinámicas.

Pasos completos de operación:

• Haz una copia de seguridad de tu libro antes de ejecutar cualquier macro por primera vez.
• Haz clic en Desarrollador > Visual Basic para abrir el editor VBA.
• Haz clic en Insertar > Módulo, luego copia y pega el código a continuación en la ventana Módulo.

Sub CountOccurrencesByPeriod()
    Dim lastRow As Long
    Dim ws As Worksheet, summaryWs As Worksheet
    Dim periodType As String
    Dim dict As Object, key As Variant
    Dim dateRange As Range, cell As Range
    Dim outputRow As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    Set dateRange = Application.InputBox("Select date range:", xTitleId, Selection.Address, Type:=8)
    
    periodType = Application.InputBox("Count by (Year/Quarter/Month/Week):", xTitleId, "Month", Type:=2)
    
    If dateRange Is Nothing Or periodType = "" Then Exit Sub
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In dateRange
        If IsDate(cell.Value) Then
            Select Case LCase(periodType)
                Case "year"
                    key = Year(cell.Value)
                Case "quarter"
                    key = "Q" & WorksheetFunction.RoundUp(Month(cell.Value) / 3, 0) & " " & Year(cell.Value)
                Case "month"
                    key = Format(cell.Value, "yyyy-mm")
                Case "week"
                    key = "W" & WorksheetFunction.WeekNum(cell.Value) & " " & Year(cell.Value)
                Case Else
                    key = Format(cell.Value, "yyyy-mm")
            End Select
            
            If dict.Exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
    Next cell
    
    Set summaryWs = Worksheets.Add(After:=ws)
    summaryWs.Name = "Occurrence_Summary"
    
    summaryWs.Range("A1").Value = "Period"
    summaryWs.Range("B1").Value = "Occurrences"
    
    outputRow = 2
    For Each key In dict.Keys
        summaryWs.Cells(outputRow, 1).Value = key
        summaryWs.Cells(outputRow, 2).Value = dict(key)
        outputRow = outputRow + 1
    Next key
    
    MsgBox "Summary completed in sheet 'Occurrence_Summary'.", vbInformation
End Sub

Después de ingresar el código:

• Vuelve a Excel y presiona Alt+F8, selecciona ContarOcurrenciasPorPeriodo y haz clic en Ejecutar.
• Un mensaje te pedirá que selecciones el rango de fechas para analizar. Selecciona la columna o rango relevante que contiene tus fechas.
• Un segundo mensaje te pedirá el período por el cual agrupar: ingresa "Año", "Trimestre", "Mes" o "Semana" (no distingue mayúsculas/minúsculas).
• La macro generará una nueva hoja de trabajo llamada Resumen_Ocurrencias listando cada período y el recuento de ocurrencias dentro de él.

Solución de problemas y consejos:

• Si encuentras una advertencia de seguridad de macros, ajusta la configuración de macros en Archivo > Opciones > Centro de confianza > Configuración de macros.
• Asegúrate de que tu columna de fecha contenga valores de fecha válidos de Excel; cadenas de texto o formatos mixtos pueden producir recuentos inexactos o errores.
• La macro es flexible—ingresa "Trimestre" para agrupar rápidamente recuentos por año y trimestre, o "Semana" para resumir semanalmente.
• Si deseas personalizar la salida (por ejemplo, agregar más detalles), puedes modificar la macro para procesar columnas adicionales o reglas de cálculo.

Esta solución es robusta para informes por lotes o análisis periódicos, pero supone un conocimiento básico de VBA y una gestión adecuada del libro. Si deseas combinar resúmenes visuales, considera usar tanto tablas dinámicas como VBA.

Contar la frecuencia de entradas o eventos por semana es un requisito común en el seguimiento de ventas, gestión de proyectos y asignación de recursos. Excel proporciona la función NUM.DE.SEMANA, que devuelve el número de semana de una fecha dada dentro de un año, facilitando agrupar datos semanalmente usando fórmulas.

Escenario aplicable: Tienes una lista de fechas (por ejemplo, datos de ventas o asistencia) y quieres contar cuántas entradas caen en cada semana del año. Este método funciona bien para análisis continuos y cuando tus datos cambian con frecuencia, ya que el recuento se actualiza automáticamente.

1. En una columna vacía (por ejemplo, B2), ingresa la siguiente fórmula para calcular el número de semana para cada fecha en la columna A:

=WEEKNUM(A2,1)

El segundo argumento ("1") indica que las semanas comienzan el domingo (cámbialo a "2" si deseas que las semanas comiencen el lunes). Copia esta fórmula hacia abajo para todas las filas de tus datos de fecha.

2. Haz una lista de números de semana que deseas resumir (por ejemplo, 1, 2, 3, ...). En otra celda en blanco (digamos, D2), usa la siguiente fórmula para contar las ocurrencias para un número de semana específico (asumiendo que B2:B24 enumera los números de semana y D2 contiene la semana a buscar):

=COUNTIF($B$2:$B$24, D2)

Después de presionar Enter, arrastra esta fórmula hacia abajo para tu lista de números de semana. Cada resultado muestra el recuento de ocurrencias para esa semana.

Consejos y precauciones:

• Si deseas contar por año y semana, para distinguir entradas entre diferentes años, usa:

  =SUMPRODUCT((YEAR($A$2:$A$24)=$F$2)*(WEEKNUM($A$2:$A$24,1)=G2))

  Donde F2 es el año objetivo y G2 es el número de semana objetivo. Ajusta los rangos de columnas y referencias según sea necesario.
• La numeración de semana de la función NUM.DE.SEMANA puede variar dependiendo de la configuración (sistema, EE.UU./ISO, tu día de inicio elegido).
• Si usas números de semana ISO (estándar europeo, semanas que comienzan los lunes y la primera semana es aquella con el primer jueves), usa =NUM.ISO.SEMANA(A2) (para Excel 2013 y posterior).
• Asegúrate siempre de que todos tus valores de fecha estén en formato de fecha válido de Excel para resultados precisos.

Este método es flexible para tablas de datos dinámicas y se puede adaptar para tableros, resúmenes periódicos y cuando deseas cruzar tabular recuentos por semana sin usar tablas dinámicas o complementos adicionales.

Cuenta el número de fines de semana/días laborables entre dos fechas en Excel

Contar si por fecha/mes/año y rango de fechas en Excel

Contar si datos/lista filtrados con criterios en Excel