Consejos de Excel: Contar/sumar celdas por color (fondo, fuente, formato condicional)

En las tareas diarias, el marcado de colores es un método popular para distinguir y resaltar rápidamente datos cruciales. Pero, ¿cómo contamos o sumamos los datos de las celdas en función de un color específico (color de relleno, color de fuente, formato condicional)? Por defecto, Excel no ofrece una función directa para contar o sumar por color. Sin embargo, con algunos trucos y métodos indirectos, aún podemos lograrlo. Este artículo explorará cómo contar o sumar datos por color.

Contar y sumar celdas en función del color de fondo

• Mediante el uso de una Función Definida por el Usuario
• Mediante el uso de una característica poderosa: Kutools para Excel
• Mediante el uso de Filtro y SUBTOTAL

Contar y sumar celdas en función del color de fuente

• Mediante el uso de una Función Definida por el Usuario
• Mediante el uso de una característica fácil: Kutools para Excel

Contar y sumar celdas en función del color de formato condicional

• Mediante el uso de código VBA
• Mediante el uso de una característica inteligente: Kutools para Excel

Video: Contar y sumar celdas en función del color

Contar y sumar celdas en función del color de fondo

Por ejemplo, si tienes un rango de datos donde los valores están rellenados con diferentes colores de fondo como se muestra en la captura de pantalla a continuación. Para contar o sumar las celdas en función de un color específico, Excel no ofrece una función directa para contar o sumar celdas según su color de fondo. Sin embargo, con un poco de ingenio y algunas técnicas útiles, puedes realizar esta tarea. Exploraremos algunos métodos útiles en esta sección.

Contar y sumar celdas por color de fondo con una Función Definida por el Usuario

Aquí te mostraremos cómo crear y usar dicha Función Definida por el Usuario para resolver esta tarea en Excel. Por favor, sigue los siguientes pasos:

Paso 1: Abre el editor del módulo VBA y copia el código

1. Presiona Alt + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.
2. En la ventana abierta, haz clic en Insertar > Módulo para crear un nuevo módulo en blanco.
3. Luego, copia y pega el siguiente código en el módulo en blanco.
   Código VBA: Contar y sumar celdas en función del color de fondo

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

Paso 2: Crea fórmulas para contar y sumar celdas por color de fondo

Después de pegar el código anterior, cierra la ventana del módulo, luego aplica las siguientes fórmulas:

• Contar celdas en función del color de fondo específico:
  Copia o escribe la fórmula proporcionada a continuación en la celda deseada para obtener el resultado. Luego, arrastra el controlador de relleno hacia abajo para obtener otros resultados. Ver captura de pantalla:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  Nota: En esta fórmula, G2 es la celda de referencia con el color de fondo específico que deseas coincidir; $B$2:$E$12 es el rango donde deseas contar el número de celdas del color de G2; FALSO se utiliza para contar celdas con color coincidente.
  
• Sumar celdas en función del color de fondo específico:
  Copia o escribe la fórmula proporcionada a continuación en la celda deseada para obtener el resultado. Luego, arrastra el controlador de relleno hacia abajo para obtener otros resultados. Ver captura de pantalla:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Nota: En esta fórmula, G2 es la celda de referencia con el color de fondo específico que deseas coincidir; $B$2:$E$12 es el rango donde deseas contar el número de celdas del color de G2; VERDADERO se utiliza para sumar celdas con color coincidente.
  

Contar y sumar celdas por color de fondo con una característica poderosa

Para aquellos que no están familiarizados con la programación, VBA puede parecer bastante complejo. Aquí, presentaremos una herramienta poderosa: Kutools para Excel, su función Contar por Color te permite calcular fácilmente (contar, sumar, promediar, etc.) en función del color de fondo con solo unos pocos clics. Impresionantemente, la función Contar por Color va más allá de los colores de fondo simples: también puede diferenciar y calcular en función de los colores de fuente y el formato condicional.

Después de descargar e instalar Kutools para Excel, primero selecciona el rango de datos que deseas contar o sumar celdas en función de un color de fondo específico. Luego, navega a Kutools Plus y elige Contar por Color.

En el cuadro de diálogo Contar por Color, especifica las operaciones:

1. Selecciona Formato estándar de la lista desplegable Método de color;
2. Especifica Fondo de la lista desplegable Tipo de conteo, y puedes previsualizar los resultados estadísticos para cada color de fondo en el cuadro de diálogo;
3. Finalmente, haz clic en Generar informe para exportar los resultados calculados a un nuevo libro de trabajo.

Resultado:

Ahora obtendrás un nuevo libro de trabajo con las estadísticas. Ver captura de pantalla:

Contar y sumar celdas por color de fondo con Filtro y función SUBTOTAL

Supongamos que tenemos una tabla de ventas de frutas como se muestra en la captura de pantalla a continuación, y contaremos o sumaremos las celdas coloreadas en la columna Cantidad columna.

Paso 1: Aplica la función SUBTOTAL

Selecciona celdas en blanco para ingresar la función SUBTOTAL.

• Para contar todas las celdas con el mismo color de fondo, por favor ingresa la fórmula:

  =SUBTOTAL(102, F2:F16)

• Para sumar todas las celdas con el mismo color de fondo, por favor ingresa la fórmula:

  =SUBTOTAL(109, F2:F16)

• Nota: en las fórmulas anteriores, 102 representa contar valores numéricos en una lista filtrada mientras se excluyen las celdas ocultas; 109 representa sumar valores en una lista filtrada excluyendo celdas ocultas; F2:F16 es el rango sobre el cual se calculará el recuento o la suma.
• 

Paso 2: Filtra celdas en función de un color específico

1. Selecciona el encabezado de la tabla, y haz clic en Datos > Filtro. Ver captura de pantalla:
   
2. Haz clic en el Filtro icono  en la celda del encabezado de la Cantidad columna, y haz clic en Filtrar por Color y el color especificado que contarás sucesivamente. Ver captura de pantalla:
   

Resultado:

Después de filtrar, las fórmulas SUBTOTAL cuentan y suman automáticamente las celdas coloreadas en la columna Cantidad Ver captura de pantalla:

Contar y sumar celdas en función del color de fuente

¿Quieres contar o sumar celdas en función de su color de fuente en Excel? Digamos que tienes los datos, como en la captura de pantalla dada, con celdas que contienen textos en rojo, azul, naranja y negro. Excel no facilita esto por defecto. ¡Pero no te preocupes! En esta sección, te mostraremos algunos trucos simples para hacer precisamente eso.

Contar y sumar celdas en función del color de fuente con una Función Definida por el Usuario

Para contar y sumar celdas con colores de fuente específicos, la siguiente Función Definida por el Usuario puede ayudarte a resolver esta tarea. Por favor, sigue los siguientes pasos:

Paso 1: Abre el editor del módulo VBA y copia el código

1. Presiona Alt + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.
2. En la ventana abierta, haz clic en Insertar > Módulo para crear un nuevo módulo en blanco.
3. Luego, copia y pega el siguiente código en el módulo en blanco.
   Código VBA: Contar y sumar celdas en función del color de fuente

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

Paso 2: Crea fórmulas para contar y sumar celdas por color de fuente

Después de pegar el código anterior, cierra la ventana del módulo, luego aplica las siguientes fórmulas:

• Contar celdas en función del color de fuente específico:
  Copia o escribe la fórmula proporcionada a continuación en la celda deseada para obtener el resultado. Luego, arrastra el controlador de relleno hacia abajo para obtener otros resultados. Ver captura de pantalla:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Nota: En esta fórmula, G2 es la celda de referencia con el color de fuente específico que deseas coincidir; $B$2:$E$12 es el rango donde deseas contar el número de celdas del color de G2.
  
• Sumar celdas en función del color de fuente específico:
  Copia o escribe la fórmula proporcionada a continuación en la celda deseada para obtener el resultado. Luego, arrastra el controlador de relleno hacia abajo para obtener otros resultados. Ver captura de pantalla:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Nota: En esta fórmula, G2 es la celda de referencia con el color de fuente específico que deseas coincidir; $B$2:$E$12 es el rango donde deseas contar el número de celdas del color de G2.
  

Contar y sumar celdas en función del color de fuente con una característica fácil

¿Buscas contar o sumar valores de celdas en Excel basándote en el color de fuente sin esfuerzo? ¡Explora la función Contar por Color de Kutools para Excel! Con esta herramienta inteligente, contar y sumar celdas por un color de fuente específico se vuelve muy sencillo. Descubre cómo Kutools puede transformar tu experiencia en Excel.

Después de descargar e instalar Kutools para Excel, primero selecciona el rango de datos que deseas contar o sumar celdas en función de un color de fuente específico. Luego, haz clic en Kutools Plus > Contar por Color para abrir el cuadro de diálogo Contar por Color.

En el cuadro de diálogo Contar por Color, especifica las operaciones:

1. Selecciona Formato estándar de la lista desplegable Método de color;
2. Especifica Fuente de la lista desplegable Tipo de conteo, y puedes previsualizar los resultados estadísticos para cada color de fuente en el cuadro de diálogo;
3. Finalmente, haz clic en Generar informe para exportar los resultados calculados a un nuevo libro de trabajo.

Resultado:

Ahora, tienes un nuevo libro de trabajo que muestra las estadísticas detalladas basadas en el color de fuente. Ver captura de pantalla:

Contar y sumar celdas en función del color de formato condicional

En Excel, comúnmente usas el Formato Condicional para aplicar un color específico a las celdas que cumplen ciertos criterios, haciendo que la visualización de datos sea intuitiva. Pero, ¿qué pasa si necesitas contar o sumar esas celdas especialmente formateadas? Aunque Excel no ofrece una forma directa para esto, aquí hay formas de sortear esta limitación.

Contar y sumar celdas con formato condicional con código VBA

Contar y sumar celdas con formato condicional en Excel no es sencillo utilizando funciones integradas. Sin embargo, puedes lograr esta tarea usando código VBA. Veamos cómo puedes usar VBA para esto:

Paso 1: Abre el editor del módulo VBA y copia el código

1. Presiona Alt + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.
2. En la ventana abierta, haz clic en Insertar > Módulo para crear un nuevo módulo en blanco.
3. Luego, copia y pega el siguiente código en el módulo en blanco.
   Código VBA: Contar y sumar celdas en función del color de formato condicional

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

Paso 2: Ejecuta este código VBA

1. Después de pegar el código, presiona F5 para ejecutar este código, aparecerá un cuadro de diálogo, selecciona el rango de datos donde deseas contar y sumar celdas en función del formato condicional. Luego, haz clic en Aceptar, Ver captura de pantalla:
   
2. En otro cuadro de diálogo, selecciona un color de formato condicional específico que deseas contar y sumar, y haz clic en Aceptar botón, ver captura de pantalla:
   

Resultado:

Ahora, el resultado, que incluye tanto el recuento como la suma de las celdas con el color de formato condicional especificado, se mostrará en el cuadro emergente. Ver captura de pantalla:

Contar y sumar celdas con formato condicional con una característica inteligente

Si buscas otros métodos rápidos y fáciles para contar y sumar celdas con formato condicional, Kutools para Excel es tu solución preferida. Su función Contar por Color puede resolver esta tarea en solo unos pocos clics. Sumérgete para descubrir la eficiencia y precisión que Kutools puede aportar a tu flujo de trabajo.

Después de descargar e instalar Kutools para Excel, primero selecciona el rango de datos que deseas contar o sumar celdas en función de un color de formato condicional específico. Luego, haz clic en Kutools Plus > Contar por Color para abrir el cuadro de diálogo Contar por Color.

En el cuadro de diálogo Contar por Color, especifica las operaciones:

1. Selecciona Formato condicional de la lista desplegable Método de color;
2. Especifica Fondo de la lista desplegable Tipo de conteo, y puedes previsualizar los resultados estadísticos para cada color de formato condicional en el cuadro de diálogo;
3. Finalmente, haz clic en Generar informe para exportar los resultados calculados a un nuevo libro de trabajo.

Resultado:

Ahora, tienes un nuevo libro de trabajo que muestra las estadísticas detalladas basadas en el color de formato condicional. Ver captura de pantalla:

Artículos Relacionados: