Excel no tiene una función que sume las celdas según su color de relleno, porque el color es un formato y no un dato. Aun así, hay tres formas de hacerlo: filtrar por color y sumar con SUBTOTAL, buscar las celdas por formato o crear una función personalizada con VBA. ¿Cuál conviene en cada caso y qué limitaciones tiene cada una?
Sumar por color con un filtro y SUBTOTAL.
Es la forma más sencilla y no requiere macros. Los pasos son estos:
- En una celda aparte, escribir =SUBTOTAL(9;B2:B100), donde B2:B100 es el rango con los valores. El 9 indica suma, y SUBTOTAL solo suma las filas visibles.
- Activar el filtro con «Datos», «Filtro» o Ctrl + Mayús + L.
- En la flecha del encabezado de la columna de valores, elegir «Filtrar por color» y el color que se quiere sumar.
El resultado de SUBTOTAL cambia y muestra la suma de las celdas de ese color. Para ver otro color, se cambia el filtro. Con =SUBTOTAL(3;B2:B100) se cuentan las celdas visibles en lugar de sumarlas.
El filtro por color también funciona con los colores del formato condicional. El uso de los filtros y de SUBTOTAL se explica en el artículo sobre para qué sirven los filtros en Excel.
- Liquidador de pensiones en Colpensiones
- Liquidador de intereses judiciales y comerciales
- Liquidador de renta personas naturales
Sumar por color con Buscar.
Para un cálculo rápido sin escribir fórmulas, se puede usar la herramienta Buscar:
- Seleccionar el rango de valores y presionar Ctrl + B.
- Hacer clic en «Opciones», luego en «Formato», «Elegir formato de celda», y hacer clic en una celda del color buscado.
- Hacer clic en «Buscar todos» y luego presionar Ctrl + E para seleccionar todas las celdas encontradas.
Al cerrar la ventana, las celdas de ese color quedan seleccionadas y la barra de estado, en la parte inferior de Excel, muestra su suma, su promedio y su cantidad. El resultado no queda en ninguna celda, de modo que sirve para consultar, no para un informe.
Función personalizada para sumar por color.
Si se necesita que la suma quede en una celda con fórmula, hay que crear una función en VBA. Se abre el editor con Alt + F11, se elige «Insertar», «Módulo» y se pega este código:
Function SumarColor(celdaColor As Range, rango As Range) As Double
Dim celda As Range
Application.Volatile
For Each celda In rango.Cells
If celda.Interior.Color = celdaColor.Cells(1, 1).Interior.Color Then
If IsNumeric(celda.Value) And Not IsEmpty(celda.Value) Then
SumarColor = SumarColor + celda.Value
End If
End If
Next celda
End Function
La función recorre cada celda del rango y, si su color de relleno es igual al de la celda de referencia y su contenido es un número, suma el valor. Se usa como cualquier otra función:
=SumarColor(E2;B2:B100)E2 es una celda pintada con el color que se quiere sumar y B2:B100, el rango de valores. Para sumar otro color se cambia la celda de referencia o se pinta E2 de otro color. Para contar en lugar de sumar, la función ContarColor del archivo descargable sigue la misma lógica.
El libro debe guardarse como «Libro de Excel habilitado para macros (.xlsm)», y quien lo abra debe habilitar el contenido.
Limitaciones de la función personalizada.
Antes de usarla, conviene conocer tres limitaciones:
- No se actualiza sola al cambiar un color. Cambiar el relleno de una celda no hace que Excel recalcule. Application.Volatile hace que la función se recalcule con cualquier cambio en el libro, pero si lo único que cambió fue un color, hay que presionar F9 o Ctrl + Alt + F9.
- No reconoce los colores del formato condicional. La propiedad Interior.Color devuelve el relleno aplicado a mano, no el que aplica el formato condicional. Y la propiedad que sí lo detecta, DisplayFormat, no funciona dentro de una función usada en una celda.
- Depende de las macros. Si el libro se guarda como .xlsx o se abre con las macros deshabilitadas, la fórmula muestra #¿NOMBRE?.
Por estas razones, la función conviene solo cuando los colores se aplican a mano y no cambian con frecuencia.
La alternativa recomendada: una columna con la categoría.
Cuando los colores provienen de un formato condicional, la suma por color es innecesaria: si las celdas se pintan de rojo porque cumplen una condición, basta con sumar las que cumplen esa misma condición. Por ejemplo, si el saldo se colorea cuando la cartera está vencida, se suma con SUMAR.SI sobre el criterio que define el color.
Y cuando el color se aplica a mano para indicar un estado, como «al día», «por vencer» o «vencido», de acuerdo con nuestro criterio es mejor registrar ese estado en una columna auxiliar y sumar con SUMAR.SI:
=SUMAR.SI($C$2:$C$100;"Vencido";$B$2:$B$100)El resultado se actualiza solo, funciona sin macros y en cualquier versión, y el color puede seguir aplicándose con formato condicional a partir de la columna del estado.
Descargar los ejemplos.
El archivo de Excel tiene una lista de saldos coloreados por estado, con el filtro activo y SUBTOTAL para sumar por color, y la suma por categoría con SUMAR.SI. El archivo. Descargar archivo.
A continuación se dejan las macros de ejemplo:
Function SumarColor(celdaColor As Range, rango As Range) As Double
Dim celda As Range
Application.Volatile
For Each celda In rango.Cells
If celda.Interior.Color = celdaColor.Cells(1, 1).Interior.Color Then
If IsNumeric(celda.Value) And Not IsEmpty(celda.Value) Then
SumarColor = SumarColor + celda.Value
End If
End If
Next celda
End Function
Function ContarColor(celdaColor As Range, rango As Range) As Long
Dim celda As Range
Application.Volatile
For Each celda In rango.Cells
If celda.Interior.Color = celdaColor.Cells(1, 1).Interior.Color Then
ContarColor = ContarColor + 1
End If
Next celda
End Function
Preguntas frecuentes.
A continuación respondemos las preguntas frecuentes de nuestros lectores.
¿Existe una fórmula para sumar por color en Excel?
No hay una función propia. Sin macros, se filtra por color y se suma con =SUBTOTAL(9;rango). Con macros, se crea una función personalizada.
¿Por qué la suma por color no se actualiza al cambiar un color?
Porque cambiar un formato no hace que Excel recalcule. Hay que presionar F9 después del cambio.
¿Por qué la función me da #¡VALOR!?
Suele ocurrir cuando el rango tiene textos y la función no los excluye, o cuando los argumentos están en orden distinto al de la función. La versión de este artículo suma solo los valores numéricos.
¿Se pueden sumar los colores del formato condicional?
Con el filtro por color y SUBTOTAL, sí. Con la función personalizada, no; en ese caso es mejor sumar con SUMAR.SI sobre la misma condición que aplica el color.
¿Cómo cuento las celdas de un color?
Filtrando por color y usando =SUBTOTAL(3;rango), o con la función ContarColor del archivo descargable.
Twitter
Facebook
WhatsApp
LinkedIn