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.
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
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.
Cristopher dice
Muchas gracias excelente.
Tengo una duda, hice esto, pero mi archivo ya no me deja copiar y pegar , a que se debe?
laura cavazos dice
ya la aplique y me funciono , pero cambie el color y nunca se modifico el resultado. que hago?? quiero poder cambiar los colores y que me aplique la formula
Sanson Montecarlo dice
no suma si la celda contiene formula, si es numero digitado si. que se debe hacer para que suma, espero respuesta
Wilfrido dice
En caso que necesite solo sumar la celda de un definido color, sin tomar en cuenta un valor dentro de la celda, ¿como podria realizarlo? Es decir solo sumara los recuadros o celdas de un definido color.
Saludos!
Felipe dice
Excelente aporte.
Rafa dice
Hola,
Muy interesante y entendible para usuarios como yo con un nivel muy básico. He conseguido trasladarlo a mi excel, pero el problema es que el resultado no se actualiza con cada cambio en una celda. He seguido estas indicaciones siguientes, pero no lo he conseguido. ¿Dónde puede estar el error?
“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”
Muchas gracias!!
Saludos,
Rafa.
Manuel Ponce dice
Muy Interesante , los temas que son de gran ayuda
Ricardo dice
gracias, me ha sido de mucha ayuda…..muchas gracias.
saludos.
Ricardo – IX Region – Chile
Edgar dice
Muy bueno y al momento de formar mi propio cuadro… seria genial… con los colores hasta predeterminados…. Dios te Bendiga…
Jesús M dice
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.
Jose dice
Como siempre fenomenal un saludo.