Sumar en Excel según el color de la celda
Hemos recibido la petición de algunos usuarios, para publicar una forma de sumar valores en Excel, según el color de la celda donde se encuentras esos valores.
Cuando trabajamos con formatos condicionales, le colocamos un determinado color a las celdas que cumplan con ciertos requisitos, luego podemos necesitar sumar todas las celdas de color azul, por ejemplo.
Desafortunadamente Excel no tiene una formula o una función que realice esta tarea, por lo que tendremos que construir una función personalizada.
Para ello vamos a “Programador”, “Visual Basic” e insertamos un nuevo modulo [Esto para office 2007]
En office 2003 o anteriores, vamos a “Herramientas”, “Macros”, “Editor Visual Basic” e insertamos un nuevo modulo.
Una vez hayamos abierto nuestro modulo, en el editor de Visual Basic, pegaremos el siguiente código:
Function Sumarcolor(Celdacolor As Range, Rangosuma As Range) As Double
Dim celda As Range
For Each celda In Rangosuma
If celda.Interior.ColorIndex = Celdacolor.Cells(1, 1).Interior.ColorIndex Then Sumarcolor = Sumarcolor + celda
Next celda
Set celda = Nothing
End Function
Guardamos el archivo, volvemos a Excel, y en la celda en la que queremos colocar el resultado de la suma, escribiremos la siguiente fórmula:
=Sumarcolor(B2;A2:A15)
En esta fórmula estamos suponiendo que los valores están en la columna A desde la fila 2 hasta la fila 15, por eso el rango A2:A15.
La celda B2, es la celda que tendrá el color que queremos sumar. En este caso queremos sumar la celdas de color azul del rango A2:A14, entonces la celda B2 la colocamos de color azul.
Si queremos sumar otros colores, todo lo que hay que haces es cambiar el color de la celda referencia, que en este caso es la celda B2, pero cada quien podrá elegir cualquier celda, teniendo presente que deberá modificar la fórmula según el rango de la celda referencia.
Una vez se cambie el color de la celda referencia, la celda donde se encuentra la formula, debe ser actualizada, y para ello nos ubicamos en la celda respectiva, presionamos F2 y luego Enter.
Código tomado de Funciones personalizadas
Nota: Tenga en cuenta que para que la fórmula funcione correctamente, las macros deben estar habilitadas. Excel se encargará de informarle mediante una advertencia de seguridad que las macros se han deshabilitado, y le ofrecerá la opción para que las habilite.
hola!
ya hice los pasos en visual basic y todo quedo muy bien,
ahora el problema que tengo es que cuando quiero cerrar
la hoja de excel o en si el programa, me dice que el macro no esta guardado, que si guardo el documento sin el macro.
como hago para poder guardarlo y que quede como ya una formula en el excel.
enviame el archivo a gyaserviciosyconsultoria@gmail.com yo te ayudo con eso..
que onda,
pues yo namas quiero los pasos para poder guardar la formula esa de sumarcolor y que no se me borre al cerrar excel..
necesito que la formula solo cuenta la celda (sin valor) que tiene como fondo el color amarillo, como una mas ..osea que si tengo dos celdas sin valores con fondo amarillo el resultado sea 2…
es muy facil, solo sustituye esto:
En vez de “Sumarcolor = Sumarcolor + celda”
Coloca “Sumarcolor = Sumarcolor + 1″
Es todo, Saludos
la formula me funciona cuando hago todo el procedimiento pero en lo que coloco las celdas correspondientes al calculo que necesito “=sumacolor(Q2;G2:K2)” me da el mensaje #¿NOMBRE? y no calcula nada ademas necesito arrastras ese calculo 2408 celdas hacia abajo tampoco he podido hacer eso
a mi tambien me pasa lo mismo, solo 1 vez lo pude hacer, ya despues me aparece puro signo de “#” y cuando pones el cursor en cima pone el mensaje ese..
estoy de-acuerdo con GaD0
La funcion me funciona perfecto pero tengo un problema
Si actualizo un color y lo cambio el valor que me da la funcion no cambia excepto cuando hago enter con ella. Existe alguna forma de que funcione “en tiempo real” ? Gracias!
¿hay algún código para actualizarlo en tiempo real?
Buenas tardes alguien me puede ayudar con algun codigo, quiero poner varios rangos a la funcion de =sumarcolor , algo parecido a la de =sumar.si, empezando por el rango del color . help¡¡¡
SOCORRO!!
Buenas queridos amigos, :decabeza:
Un amigo y yo llevamos días intentando finalizar una macro para una aplicación de turnos de nuestro trabajo. Hemos desplegado los días del año en la columna B y los nombres en la fila 6. El caso es que la siguiente macro nos permite variar el color según el indicativo de la celda inferior derecha (rango de cada casilla de turnos 2:3)
Pues bien, hemos conseguido que al indicar en esta celda inferior derecha, “BAJA”, “VAC”, “L”, etc… copie el valor de la celda que se encuentra a la izquierda de esta, en un rango de celdas que se encuentra mucho más abajo (3000 filas más abajo) para después sumarlos o tratarlos según nos interese. [B]Sin embargo nuestra macro no consigue leer el dato si es copiado y pegado!!! SOS!!![/B] Nos vamos a volver locos!!! Hemos intentado de todo…. y cuando digo de todo… es de todo.
[QUOTE]Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column / 2) = Int((Target.Column / 2)) And Target.Row <= 2529 Then
Select Case Target.Value
Case Is = "BAJA", "baja"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 27
'OLI: ESTE CASE HACE REFERENCIA A LAS HORAS INDICADAS EN EL CUADRO
Case Is = "BAJAH", "bajah"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 27
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 27
'OLI: ESTE CASE HACE REFERENCIA A LAS HORAS INDICADAS EN EL CUADRO
Case Is = "HSH", "hsh"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 45
'OLI: ESTE CASE HACE REFERENCIA A LAS HORAS INDICADAS EN EL CUADRO
Case Is = "CUH", "cuh"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 37
Case Is = "VAC", "vac"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 35
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 35
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 35
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 35
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 35
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 35
Case Is = "EXC", "exc"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 16
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 16
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 16
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 16
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 16
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 16
Case Is = "NO", "no"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 15
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 15
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 15
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 15
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 15
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 15
Case Is = "LR", "lr"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 38
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 38
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 38
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 38
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 38
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 38
Case Is = "LNR", "lnr"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 46
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 46
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 46
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 46
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 46
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 46
Case Is = "ABS", "abs"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 3
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 3
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 3
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 3
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 3
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 3
Case Is = "SES", "ses"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 9
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 9
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 9
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 9
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 9
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 9
Case Is = "L", "l", " "
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 2
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 2
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 2
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 2
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 2
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 2
Case Is = "JT", "OFI", "SUP", "GES", "RRHH", "jt", "ofi", "sup", "ges", "rrhh"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 39
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 39
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 39
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 39
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 39
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 39
Case Is = "HS", "hs"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 45
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 45
Case Is = "CU", "cu"
Cells(LTrim(Str(Target.Row)), Target.Column).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)) – 1, Target.Column).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)), Target.Column – 1).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)) – 1, Target.Column – 1).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)) – 2, Target.Column).Interior.ColorIndex = 37
Cells(LTrim(Str(Target.Row)) – 2, Target.Column – 1).Interior.ColorIndex = 37
Case Else
End Select
End If
End Sub[/QUOTE]
Saludos cordiales,
Ricardo
amigos quiero compartir con ustedes un curso de excel gratuito, de mi autoria, son 12 excelentes lecciones… mi correo es gyaserviciosyconsultoria@gmail.com
Gracias esta genial!!!
Gracias, estaba buscando como sumar celdas con texto en distinto color y adaptando tu ejemplo me ha funcionado. También he habilitado las macros según la recomendación.
Por si alguien necesita lo mismo, sumar celdas con el mismo color de fuente, ésta ha sido mi adaptación:
Function SumarFONT(CeldaFONT As Range, Rangosuma As Range) As Double
Dim celda As Range
For Each celda In Rangosuma
If celda.FONT.ColorIndex = CeldaFONT.Cells(1, 1).FONT.ColorIndex Then SumarFONT = SumarFONT + celda
Next celda
Set celda = Nothing
End Function
Y las fórmulas las escribí así:
=SUMARFONT(I19;$K$2:$K$16)
=SUMARFONT(I20;$K$2:$K$16)
=SUMARFONT(I21;$K$2:$K$16)
I19, I20, I21 contenían el color de la fuente
(Texto con los respectivos colores)
K2:K16 el rango de celdas a sumar donde estaban, en este caso, los tres colores usados
AMIGOS a todos los contadores y microempresarios, les estamos regalando un software contable, solo deben seguir las instrucciones que siguen a continuacion:
https://sites.google.com/site/gyaserviciosyconsultoria/regalo
no funciona, excel no reconoce la formula. habra que buscar otro metodo, gracias igual. saludos.
no funciona ese metodo, cree todo tal cual dice la explicacion, pero el excel no reconoce la formula,.. gracias igual, pero habra que buscar otro metodo. saludos.
Hola
La funcion me funciona perfecto pero tengo un problema:
Al cerrar el Excel y volver a abrir el archivo la fórmula no vuelve a funcionar.
¿Como hago para que quede vigente como una fórmula más?
Y para que funcione en Tiempo Real?
Gracias!
buenos dias,
a mi me pasa el mismo error, al abrir el excel de nuevo me sale error.
Pudo solucionar el problema? En caso afirmativo, cómo lo hizo?Gràcias
buenas tardes, para que la macro te funcione en todos los libros de excel que abras, debes crear la función dentro del libro PERSONAL.XLSB (para office 2010), osea, al momento de dar f11 para ingresar la sintaxis arriba descrita, buscar en la parte izquierda del editor de Visual Basic el libro PERSONAL.XLSB e insertas el complemento con la sintaxis allí, luego lo guardas y abres excel, te queda habilitada para todos libros, es si, debes tener habilitada la ejecución de macros en tu excel.. espero sirva de algo aunque sea un poco tarde.
buen dia, me gustaria que me orienten para poder encontrar una formula que pueda hacer lo siguiente: en una columna “x” de excel quiero insertar valores alfanumericos consecutivos, por ejemplo a1, a2, a3, a4, etc, etc…. el detalle es que las celdas de bajo no tienen el mismo intervalo, unas son debajo, otras son dos celdas abajo otras 3 celdas abajo. como producir ese consecutivo no teniendo las celdas una bajo la otra. espero contar con su ayuda, gracias de antemano.
AMIGOS, los invito a que se registren en http://www.aprendexcel.com y luego vayan a la seccion de descargar, encontraran excelente material de excel completamente gratis, adicionalmente una conferencia gratuita para los que se inscriban para el 25 de abril.
al crear la macro de sumarcolor el programa de excel queda muy pesado y los procesos son muy lentos, hay alguna forma de guardar el libro para hacer mas rapido el proceso.
gracias
Muy buenas tardes, a todos los colegas que usan excel a diario, los invito a que visiten la siguiente pagina:
http://www.aprendexcel.com
Hola
La funcion me funciona perfecto pero tengo un problema
Si actualizo un color y lo cambio el valor que me da la funcion no cambia excepto cuando hago enter con ella. Existe alguna forma de que funcione “en tiempo real” ? Gracias!