Suma en excel según color de celda

Seguramente en alguna momento se han preguntado si es posible hacer suma en excel según color de celda,  en formatos contables es común que usemos el color de relleno para resaltar celdas que cumplen con ciertos criterios, por ejemplo, marcamos de color rojo las facturas que están vencidas y de verde las que no están vencidas.

Sabemos que excel tiene una gran cantidad de funciones, pero… ¿hay alguna función para  hacer una suma en excel según color de celda ?
La respuesta es no… no hay una función nativa para hacer suma en excel según color de celda, pero eso no significa que no se pueda hacer, simplemente debemos programar una función personalizada o las llamadas funciones definidas por el usuario.

Para esto debemos ir al editor VBA de excel, estando en excel, presionamos la combinación de teclas ALT+F11, con esto se abre el editor de visual basic para aplicaciones.

suma en excel según color de celda - editor vba

 

De lado izquierdo tenemos el explorador de proyectos, debemos ubicar el que corresponde al archivo con el que estamos trabajando, debe decir algo así, VBAProject ( nombre archivo ), nombre archivo corresponde al nombre de nuestro archivo.

Debemos dar clic derecho en ese proyecto/ insertar/ modulo.
Del lado derecho vemos una sección en blanco, en esa parte es donde se escribe el código, en este caso debemos poner lo siguiente.

Function SumaColor(RangoDatos, CriterioColor) As Double
Dim CritColor As Single, Celda As Range
SumaColor = 0
CritColor = CriterioColor.Item(1).Interior.ColorIndex
For Each Celda In RangoDatos
If Celda.Interior.ColorIndex = CritColor Then _
SumaColor = SumaColor + Celda.Value2
Next Celda
End Function

Ese es el código de la función personalizada y su sintaxis es la siguiente: SumaColor(RangoDatos, CriterioColor)

Un ejemplo ya aplicado seria con esta formula: =SumaColor(A4:A6,C3)

El rango donde están los datos es A4:A6 y el rango donde esta el color de celda ( fondo ) que es el criterio en este caso, es la celda C3

suma en excel según color de celda - ejemplo

Como vemos en la imagen, hemos puesto el amarillo como criterio para esta suma, en el rango de datos hay dos que están marcados con ese color, así que deben sumarse solo esos dos.

Algo importante en este caso, es que las formulas siempre se re-calculan cuando se actualiza la hoja ( cuando modificamos el valor de una celda ), también se puede actualizar presionando la tecla de función F9 o presionando la tecla F2 y enter en alguna celda, pero en este caso eso no sucede al ponerle color de fondo a una celda, así que la formula no se va a calcular en ese momento.

Para solucionar esto, debemos agregar una linea de código para que la formula o la hoja completa se calcule cuando seleccionamos cualquier celda ( cambiar celda seleccionada ).
Para ello debemos ir a excel, clic derecho en el nombre de la hoja donde queremos aplicar esa función de SumaColor/ Ver código, en la ventana que nos abre debemos poner este código.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

Con esto queda completa nuestra hoja para poder hacer sumas de acuerdo al color de relleno de las celdas.

Nota: Para que esto funcione es importante habilitar macros… ver video como habilitar macros en excel.

Hacer suma en excel según color de celda ahora te sera muy simple y seguramente te facilitara enormemente tu trabajo, las aplicaciones de esto son muy variadas, si tienes algún libro de cuentas por cobrar, si aplicas formato condicional, registro de ventas, en fin, estoy seguro que le sacaras provecho.

Descargar: Suma en excel según color de celda

Comments

  1. Jesús M says

    Muy buen tip Nolberto, hace un tiempo sabia de esta forma de sumar utilizando una función definida creada en VBA, y es muy útil, en lo personal en mis formatos y bases de registros utilizo columnas auxiliares con fórmulas y formatos condicionales que sirven de candados para evitar que los usuarios puedan comerte algún error en la captura de la información.

    Hasta la próxima, Saludos.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *