Cómo utilizar CONTAR.SI y SUBTOTAL juntos en Excel

En Excel, CONTAR.SI se utiliza normalmente para contar células basándose en un único criterio, mientras que TOTAL PARCIAL está diseñado para proporcionar resultados agregados (como sumas o recuentos) que pueden excluir filas ocultas o filtradas. Al combinar estas dos funciones, obtiene la flexibilidad de aplicar criterios de conteo (a través de CONTAR.SI) y al mismo tiempo beneficiarse de cálculos con reconocimiento de filtros (a través de SUBTOTAL). En esta publicación de blog, aprenderá cómo pueden trabajar juntos para procesar eficientemente solo filas visiblesmanejar datos filtradosy más.

Comprender CONTAR.SI y SUBTOTAL

¿Qué es CONTAR.SI?

CONTAR.SI cuenta el número de celdas dentro de un rango que cumplen una condición específica.

 COUNTIF(range, criteria)

• rango: Las celdas que desea evaluar.
• criterios: La condición que debe cumplir cada celda (p. ej., “Manzanas”, “>10”, “EE.UU.”).

Casos de uso clave: contar entradas de texto, umbrales numéricos o rangos de fechas en grandes conjuntos de datos.

¿Qué es el SUBTOTAL?

SUBTOTAL calcula un agregado específico (suma, promedio, recuento, etc.) para un rango de datos. A diferencia de funciones estándar como SUMA o CONTAR, SUBTOTAL puede ignorar filas ocultas o filtradas si lo desea.

 SUBTOTAL(function_num, ref1, (ref2), ...)

• núm_función: un número (1 a 11 o 101 a 111) que determina qué función usar y si se ignoran las filas filtradas/ocultas. Por ejemplo:
  • 1 o 101 = PROMEDIO
  • 2 o 102 = CONTAR
  • 3 o 103 =CONTARA
  • 9 o 109 = SUMA
  • (etc.)
• ref1, ref2,…: Los rangos o referencias para los cuales desea el subtotal.

Casos de uso clave: Filtrar datos y recalcular sumas o recuentos únicamente visible filas.

¿Por qué combinar CONTAR.SI y SUBTOTAL?

1. Análisis de datos filtrados: Al filtrar una lista por país o producto, es posible que aún necesites la capacidad de CONTAR.SI para contar los elementos que cumplen una determinada condición.pero sólo entre filas visibles.
2. Paneles interactivos: En informes dinámicos o paneles donde las filas pueden estar ocultas, SUBTOTAL garantiza que su recuento se actualice automáticamente para coincidir solo con los datos mostrados.
3. Cálculos avanzados: Combinar estas funciones puede manejar criterios más complejos (a través de CONTAR.SI) manteniendo el conciencia de filtro de SUBTOTAL.

Diferentes formas de utilizar CONTAR.SI y SUBTOTAL juntos

1. Conteo de subtotales básico (todas las filas)

Un enfoque sencillo es utilizar SUBTOTAL con un núm_función que corresponde a CONTAR (como 2 o 102). Luego incorpore CONTAR.SI si necesita una condición además.

Ejemplo (recuento SUBTOTAL básico en la columna A):

 =SUBTOTAL(2, A2:A100)

• 2 significa «CONTAR», pero incluye filas ocultas. Si filtra filas, se excluirán del recuento.
• Alternativamente, 102 significa “CONTAR” pero ignora filas ocultas manualmente (por ejemplo, mediante Ocultar fila).

Utilice CONTAR.SI dentro o junto a SUBTOTAL para condiciones más precisas.

2. Contar sólo las filas visibles que cumplen una condición

Un truco consiste en emparejar el Función SUBTOTAL con una columna auxiliar que utiliza la lógica CONTAR.SI. Sin embargo, una única fórmula combinada puede resultar más elegante:

 =SUMPRODUCT( SUBTOTAL(103, OFFSET(A2, ROW(A2:A100)-ROW(A2), 0, 1)), --(A2:A100="Apples") )

• SUBTOTAL(103, COMPENSACIÓN(…)):
  • 103 corresponde a CONTARA ignorando filas ocultas.
  • OFFSET crea referencias para cada fila individualmente. Este truco comprueba eficazmente si cada fila es visible (y no filtrada).
• –(A2:A100=”Manzanas”) convierte VERDADERO/FALSO a 1/0.
• SUMAPRODUCTO multiplica la visibilidad de cada fila (1 para visible, 0 para oculta) por si cumple con la condición =”Manzanas” (también 1 o 0), luego suma todos los resultados.

Este enfoque imita un «CONTAR.SI solo en filas visibles».

3. Usar una columna auxiliar

Si las fórmulas matriciales parecen demasiado complejas, puede utilizar una columna de ayuda para marcar filas como 1 si cumplen con el criterio CONTAR.SI, y 0 de lo contrario. Luego aplique un SUBTOTAL estándar en esa columna auxiliar:

1. Columna auxiliar (p. ej., columna D):

    =IF(A2="Apples", 1, 0)

2. Fórmula subtotal:

    =SUBTOTAL(9, D2:D100)

   • 9 es «SUM», lo que significa que sumará los unos solo para las filas visibles (si los datos están filtrados).

4. Subtotal con múltiples condiciones

Para múltiples criterios, puede combinar la lógica de su columna auxiliar o usar fórmulas de matriz más avanzadas:

 =SUMPRODUCT( SUBTOTAL(103, OFFSET(A2, ROW(A2:A100)-ROW(A2), 0, 1)), --(A2:A100="Apples"), --(B2:B100>50) )

• Esto solo cuenta visible filas donde A=”Manzanas” Y b>50.

5. CONTAR.SI + SUBTOTAL para filtrado dinámico

Otro método es permitir la función integrada de Excel. Filtrar en el conjunto de datos (por ejemplo, usando Filtrar por país) y luego use:

 =SUBTOTAL(2, A2:A100)

o

 =SUBTOTAL(3, B2:B100)

Pero para aplicar un criterio específico como CONTAR.SI, usted:

• Utilice una columna auxiliar con una condición y luego SUBTOTAL.
• O utilice el método de matriz con SUMPRODUCT + SUBTOTAL(103,…).

Descargar archivo de ejemplo

Hemos reunido un archivo de práctica que contiene todos los datos de ejemplo y las fórmulas descritas anteriormente. Al descargar este archivo, podrá seguir cada paso de forma práctica: experimentar con filtros, ocultar filas y personalizar criterios para ver con precisión cómo CONTAR.SI y TOTAL PARCIAL trabajar juntos. Una vez que abra el archivo, asegúrese de filtrar varias columnas u ocultar filas específicas para ver cómo se actualizan estas fórmulas en tiempo real. ¡Siéntase libre de adaptar los datos para que coincidan con sus propias necesidades de análisis!

Fórmulas de Excel para utilizar la función CONTAR.SI y SUBTOTAL juntas

Errores comunes y mejores prácticas

1. Utilice la función correcta_num: Recuerda que 1-11 incluye filas ocultas, mientras que 101-111 ignora a mano filas ocultas. Para las filas filtradas, el uso SUBTOTAL estándar (por ejemplo, 2, 3, 9) también ignora las filas filtradas.
2. Fórmulas de matriz: El método SUMPRODUCTO + SUBTOTAL(103, OFFSET(…)) puede ser poderoso pero requiere comprender cómo funcionan los cálculos de matrices.
3. Columnas auxiliares: Si el enfoque de la fórmula matricial es demasiado complejo o lento en datos grandes, usar una columna auxiliar suele ser más simple y transparente.
4. Filtro versus Ocultación manual: Tenga en cuenta que las filas «filtradas» y «ocultas manualmente» se comportan de manera diferente con ciertos valores de num_función.
5. Actuación: Los conjuntos de datos grandes con muchas fórmulas matriciales pueden ralentizar Excel; considere optimizar o utilizar columnas auxiliares en esos escenarios.

Conclusión

aprendiendo a combinar CONTAR.SI y TOTAL PARCIAL permite más dinámica y consciente del filtro cuenta. Ya sea que necesite un simple recuento de datos filtrados o comprobaciones avanzadas de criterios múltiples que solo reflejen visible filas, estas técnicas pueden agiliza tus informes de Excel y analiza:

• Usar SUBTOTAL para ignorar filas filtradas u ocultas.
• Anidando con CONTAR.SI (directamente o mediante columnas auxiliares) filtra datos por condición y por visibilidad.
• Elija el enfoque correcto: Fórmulas de matriz versus columnas auxiliares versus referencias SUBTOTALES simples: elija la que mejor se adapte a su flujo de trabajo.

Al probar el archivo de ejemplo descargableobtendrá experiencia práctica con cada método. Pronto, aplicará estos conocimientos a cualquier escenario de filas ocultas o filtradas en Excel, ¡lo que hará que sus hojas de cálculo sean mucho más poderosas y eficientes!