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

En las tareas diarias, el marcado con colores es un método muy popular para distinguir y resaltar rápidamente datos importantes. Pero, ¿cómo podemos contar o sumar los datos de celdas según un color específico —ya sea por color de relleno, color de fuente o formato condicional—? Por defecto, Excel no incluye una función directa para contar o sumar por color. Sin embargo, con algunos trucos y métodos indirectos, sí es posible lograrlo. Este artículo te muestra cómo contar o sumar datos según su color.

Contar y sumar celdas según Color de fondo

• Mediante el uso de una función definida por el usuario
• Mediante el uso de una función potente – Kutools para Excel
• Mediante el uso de Filtro y SUBTOTAL

Contar y sumar celdas según Color de fuente

• Mediante el uso de una función definida por el usuario
• Mediante el uso de una función sencilla – Kutools para Excel

Contar y sumar celdas según el color Usar formato condicional

• Mediante el uso de código VBA
• Mediante el uso de una función inteligente – Kutools para Excel

Vídeo: Contar y sumar celdas según el color

Contar y sumar celdas según Color de fondo

Por ejemplo, si tiene un rango de datos en el que los valores están rellenados con distintos colores de fondo, como se muestra en la siguiente captura de pantalla, es posible que desee contar o sumar las celdas según un color específico. Excel no ofrece una función directa para contar o sumar celdas basándose en su color de fondo, pero con un poco de ingenio y algunas técnicas útiles, ¡sí puede lograrlo! En esta sección, exploraremos algunos métodos prácticos para hacerlo.

Contar y sumar celdas por Color de fondo con una función definida por el usuario

Aquí le mostramos cómo crear y usar esta función definida por el usuario para llevar a cabo la tarea en Excel. Siga estos pasos:

Paso 1: Abra el editor de módulos VBA y copie el código

1. Pulse Alt + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.
2. En la ventana abierta, haga clic en Insertar > Módulo para crear un nuevo módulo en blanco.
3. A continuación, copie y pegue el código siguiente en el módulo en blanco.
   Código VBA: Contar y sumar celdas según 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: Crear fórmulas para contar y sumar celdas por Color de fondo

Después de pegar el código anterior, cierre la ventana del módulo y, a continuación, aplique las siguientes fórmulas:

• Contar celdas según el color de fondo específico:
  Copie o escriba la fórmula que aparece a continuación en la celda deseada para obtener el resultado. Luego, arrastre el controlador de relleno hacia abajo para aplicarla a otras celdas. Consulte la 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 desea coincidir; $B$2:$E$12 es el rango en el que desea contar las celdas con el mismo color que G2; y FALSE se utiliza para contar únicamente las celdas cuyo color coincide.
  
• Suma celdas según un color de fondo específico:
  Copia o escribe la fórmula que aparece a continuación en la celda deseada para obtener el resultado. Después, arrastra el controlador de relleno hacia abajo para aplicarla al resto de celdas. Mira la 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 en el que quieres sumar las celdas del mismo color que G2; y TRUE indica que se deben sumar las celdas con el color coincidente.
  

Contar y sumar celdas por Color de fondo con una función potente

Para quienes no están familiarizados con la programación, VBA puede parecer bastante complejo. Aquí presentamos una herramienta potente: Kutools para Excel, cuya función Contar por color le permite calcular fácilmente (contar, sumar, promediar, etc.) en función del color de fondo con solo unos pocos clics. Sorprendentemente, la función Contar por color va más allá del simple color de fondo: también puede diferenciar y realizar cálculos según el color de fuente y el formato condicional.

Tras descargar e instalar Kutools para Excel, seleccione primero el rango de datos sobre el que desea contar o sumar celdas según un color de fondo específico. A continuación, vaya a KUTOOLS PLUS y elija Contar por color.

En el cuadro de diálogo Contar por color, especifique las operaciones:

1. Seleccione Formato estándaren la lista desplegable de Modo de color;
2. Especifique Fondoen la lista desplegable de Tipo de estadística, y podrá previsualizar los resultados estadísticos para cada Color de fondo en el cuadro de diálogo;
3. Por último, haga clic en Generar informe para exportar los resultados calculados a un nuevo libro de trabajo.

Resultado:

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

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

Supongamos que tenemos una tabla de ventas de frutas como la que se muestra en la siguiente captura de pantalla, y queremos contar o sumar las celdas coloreadas en la columna Importe.

Paso 1: Aplique la función SUBTOTAL

Seleccione celdas vacías para introducir la función SUBTOTAL.

• Para contar todas las celdas con el mismo Color de fondo, introduzca la fórmula:

  =SUBTOTAL(102, F2:F16)

• Para sumar todas las celdas con el mismo Color de fondo, introduzca la fórmula:

  =SUBTOTAL(109, F2:F16)

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

Paso 2: Filtre celdas según un color específico

1. Seleccione el encabezado de la tabla y haga clic en Datos > Filtro. Vea la captura de pantalla:
   
2. Haga clic en el icono de Filtro en la celda de encabezado de la columna Importe, luego haga clic en Filtrar por color y, a continuación, en el color específico por el que desea filtrar. Vea la captura de pantalla:
   

Resultado:

Tras aplicar el filtro, las fórmulas SUBTOTAL cuentan y suman automáticamente las celdas coloreadas en la columna Importe. Consulte la captura de pantalla:

Contar y sumar celdas en función de Color de fuente

¿Quiere contar o sumar celdas según su color de fuente en Excel? Imagine que tiene datos como los de la captura de pantalla adjunta, con celdas que contienen texto en rojo, azul, naranja y negro. Excel no ofrece esta funcionalidad de forma predeterminada, ¡pero no se preocupe! En esta sección le revelaremos algunos trucos sencillos para conseguirlo.

Contar y sumar celdas en función de Color de fuente con una función definida por el usuario

Para contar y sumar celdas con un color de fuente específico, la siguiente función definida por el usuario le ayudará a resolver esta tarea. Siga estos pasos:

Paso 1: Abra el editor del módulo VBA y copie el código

1. Pulse Alt + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.
2. En la ventana abierta, haga clic en Insertar > Módulo para crear un nuevo módulo en blanco.
3. A continuación, copie y pegue el código siguiente en el módulo en blanco.
   Código VBA: Contar y sumar celdas según 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: Crear fórmulas para contar y sumar celdas por Color de fuente

Después de pegar el código anterior, cierre la ventana del módulo y, a continuación, aplique las siguientes fórmulas:

• Contar celdas según el color de fuente específico:
  Copie o escriba la fórmula que aparece a continuación en la celda deseada para obtener el resultado. A continuación, arrastre el controlador de relleno hacia abajo para aplicarla a otras celdas. Consulte la 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 desea coincidir; $B$2:$E$12 es el rango en el que se contarán las celdas con el mismo color de fuente que G2.
  
• Suma celdas según el color de fuente específico:
  Copia o escribe la fórmula que aparece a continuación en la celda deseada para obtener el resultado. Después, arrastra el controlador de relleno hacia abajo para aplicarla al resto de celdas. Mira la 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 en el que se sumarán las celdas con el mismo color de fuente que G2.
  

Contar y sumar celdas en función de Color de fuente con una característica sencilla

¿Quiere contar o sumar valores de celdas en Excel según el color de fuente, sin esfuerzo? Pruebe la función Kutools para Excel de Contar por color. ¡Con esta herramienta inteligente, contar y sumar celdas por un color de fuente específico es muy sencillo! Descubra cómo Kutools puede transformar su experiencia con Excel.

Tras descargar e instalar Kutools para Excel, seleccione primero el rango de datos sobre el que desea contar o sumar celdas en función de un color de fuente específico. A continuación, haga 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, especifique las operaciones:

1. Seleccione Formato estándaren la lista desplegable de Modo de color;
2. Especifique Fuenteen la lista desplegable de Tipo de estadística, y podrá previsualizar los resultados estadísticos para cada Color de fuente en el cuadro de diálogo;
3. Por último, haga clic en Generar informe para exportar los resultados calculados a un nuevo libro de trabajo.

Resultado:

Ahora dispone de un nuevo libro de trabajo que muestra estadísticas detalladas según el color de fuente. Consulte la captura de pantalla:

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

En Excel, normalmente se utiliza el formato condicional para aplicar un color específico a las celdas que cumplen determinados criterios, lo que hace que la visualización de los datos sea más intuitiva. Pero, ¿qué ocurre si necesita contar o sumar esas celdas con formato especial? Aunque Excel no ofrece una solución directa para ello, existen formas de sortear esta limitación.

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

Contar y sumar celdas con formato condicional en Excel no es sencillo mediante las funciones integradas. Sin embargo, puede lograrlo fácilmente con código VBA. A continuación, le explicamos cómo usar VBA para ello:

Paso 1: Abra el editor del módulo VBA y copie el código

1. Pulse Alt + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.
2. En la ventana abierta, haga clic en Insertar > Módulo para crear un nuevo módulo en blanco.
3. A continuación, copie y pegue el código siguiente en el módulo en blanco.
   Código VBA: Contar y sumar celdas según el color Usar 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: Ejecute este código VBA

1. Después de pegar el código, pulse la tecla F5 para ejecutarlo. Aparecerá un cuadro de diálogo; por favor, seleccione el rango de datos en el que desea contar y sumar celdas según el formato condicional aplicado. A continuación, haga clic en Aceptar. Vea la captura de pantalla:
   
2. En otro cuadro de diálogo, seleccione el color específico del formato condicional que desee contar y sumar, y haga clic en el botón Aceptar. Vea la captura de pantalla:
   

Resultado:

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

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

Si busca otros métodos rápidos y sencillos para contar y sumar celdas con formato condicional, Kutools para Excel es su solución ideal. Su función Contar por color resuelve esta tarea con solo unos pocos clics. ¡Descubra la eficiencia y precisión que Kutools puede aportar a su flujo de trabajo!

Tras descargar e instalar Kutools para Excel, seleccione primero el rango de datos sobre el que desea contar o sumar celdas en función de un color de formato condicional específico. A continuación, haga 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, especifique las operaciones:

1. Seleccione Usar formato condicionalen la lista desplegable de Modo de color;
2. Especifique Fondoen la lista desplegable de Tipo de estadística, y podrá previsualizar los resultados estadísticos para cada color de formato condicional en el cuadro de diálogo;
3. Por último, haga clic en Generar informe para exportar los resultados calculados a un nuevo libro de trabajo.

Resultado:

Ahora dispone de un nuevo libro de trabajo que muestra estadísticas detalladas según el color aplicado mediante formato condicional. Consulte la captura de pantalla:

Artículos relacionados: