Sumar según color en Excel (Fórmula personalizada)
-->
Sumar según color en Excel (Fórmula personalizada): Las funciones siguientes =Sumarcolor_Fondo() y =Sumarcolor_fuente() no vienen por defecto en excel y han sido preparadas para cubrir necesidades que se nos pueden presentar al trabajar con esta hoja de cálculo. La primera realiza una suma tomando en cuenta el color de relleno de la celda, los criterios que utiliza son celda color y rango suma; la aplicación de la segunda es similar pero en este caso toma el color del texto es decir la fuente, los criterios que se usan en esta son iguales celda color y rango suma. A continuación puedes descargar estas funciones personalizadas y revisar mayor detalle sobre su uso.
A través de ejemplos voy a explicar el funcionamiento de cada una de estas fórmulas personalizadas, también pueden usar estas funciones de forma predeterminada o convertirlas por default a su excel, bastaría con convertir el archivo en un complemento de excel, podría ayudarles en caso lo necesiten.
¿COMO UTILIZO ESTAS FÓRMULAS?
-
=Sumarcolor_Fondo(CeldaColor, RangoCelda)
De la siguiente lista de datos queremos sumar todos los valores que tienen color de celda (relleno) rojo.
Si hacemos este cálculo manual obtendremos como valor 62 y para plasmarlo a través de la función sería así: =Sumarcolor_Fondo(A12,$B$2:$B$9) .
-
=Sumarcolor_Fuente(CeldaColor, RangoCelda)
De la siguiente lista de datos queremos sumar todos los valores que tienen color de fuente rojo.
Si hacemos este cálculo manual obtendremos como valor 21 y para plasmarlo a través de la función sería así: =Sumarcolor_fuente(B12,$C$2:$C$9).
ESTIMADO,
NO ME FUNCIONA LA MACRO…DEBO TENER EL ARCHIVO «SUMAR-SEGUN-COLOR» Y EL QUE QUEIRO OCUPAR ABIERTOS Y MACROS HABILITADFAS?…
POR QUE ASI LO TENGO PERO CUANDO COPIO LA FORMULA A MI ARCHIVO, NO CALCULA Y DICE «#NOMBRE?»…
ESTARÉ HACIENDO ALGO MAL?…HELP ME
Hola, escribanos al correo: [email protected] para poder ayudarle. Saludos.
Gracias.
Hola, de nada, saludos.
Buenas, estoy interesado en hacer lo siguiente, tengo un rango de celdas, en esas celdas tengo números rojos y negros, quiero que me sume los rojos, pero si cambio el color del rojo al negro se actualice, ¿como puedo hacerlo?
Hola, deberías utilizar application.volatile. Saludos.
MUY INTERESANTE E INSTRUCTIVO. GRACIAS
Gracias por su comentario, saludos.
como puedo usar de forma predeterminada o convertirlas por default a excel, bastaría con convertir el archivo en un complemento de excel, me podria aydar con esto por favor este archivo de sumar colo_fondo
Daniel, debes hacer un complemento addin y colocarlo en la carpeta complemento que esta dentro de la carpeta microsoft.
Disculpa me puedes ayudar, quiero que excel me cambie un numero por letra, me explico al colocar un numero en una celda, me aparezca en una celda determinada en letras. gracias
Omar, eso podrias hacerlo con funciones de excel, con la función sí y la función buscarv.
Hola, quiero felicitarte por tan importante información, y mantenernos al día con esta maravillosa herramienta. gracias.
Listo!! Logre que funcionara.
Gracias
Hola! Lo que yo necesito es contar las celdas que están en un determinado color sin importar el valor del número que está dentro de cada celdas, quiero contar solamente las celdas. Cómo puedo hacer esto?
Este enlace podría ayudarte:
https://www.extendoffice.com/es/documents/excel/1155-excel-count-sum-cells-by-color.html
Buenas tardes,
y si ya quisieramos darle una vuelta de tuerca mas y quisieramos sumar por color de la fuente y de celda.
Osea quiero decir, tengo varias celdas del mismo color que harán una suma independiente y dentro de esas celdas tendré un color de fuente que harán otra suma independiente pero ese mismo de color de fuente puede estar dentro de otra celda de color diferente.
¿Me podeis ayudar?
Muchas gracias.
Buenas: genial aportación, pero no me funciona guardando el código en el libro PERSONAL.XLSB, para pueda utilizarlo en cualquier libro.
Buenas!! la formula es una maravilla, pero me sale #NOMBRE? cuando el libro excel que utilizo no es el vuestro. Creo que he hecho todo: habilitar macros, copiar formula en VBA, ejecutarla, no tengo nada venido de formato condicional… que estoy haciendo mal?
Julia la función solo funciona en el libro original,si quieres pasarlo a otro libro deberias copiar el codigo de la macro en el nuevo libro.
solo habilita el macro… cuando abras la plantilla… revisa que este de acuerdo a lo anterior señalado… y te funcionará
Excelente aporte!
Solo tengo una duda, como puedo hacerle para no tenerle que teclear «F2» a la celda con la función «sumarcolor_fuente» para que se actualice en numero??
Gracias!
Este tipo de formula debe ser actualizable para poder tener buen resultado.
Gracias por el aporte, cuando le doy colores a la celda me funciona perfecto, pero si tengo formato condicional para establecer el fondo según criterios no me funciona.
Hola Alfredo, lo que pasa es que el formato condicional «no pinta» la celda realmente, solo la resalta del color que le indiques. Saludos
Esta súper increíble, muy practica y útil al trabajar con matrices. Gracias por sus invaluables aportes
Muchas Gracias por la aportación.
Esta mañana he estado trabajando con ella y me funcionaba perfectamente, he cerrado el archivo y cuando he vuelto de comer y he vuelto a trabajar en él, de repente la formula ha fallado #¿NOMBRE? y no consigo que vuelva a funcionar…..¿como puedo solucionarlo? Gracias de nuevo.
Debes habilitar macros, recuerda que las formulas son hechas en visual basic.
Buenas tardes. Estaba buscando este tipo de solucion para una tabla que tengo. Pero como. Creo el Macro? Mi idea es que si en la celda x esta el color rojo y es lunes, de un numero, si es martes, otro, asi sucesivamente. Gracias
Manuel lee sobre formato condicionalcon criterio de una formula, es la solución a lo que necesitas.
Gustavo, buenas tardes.
No me resulta la formula, los colores son asignados de acuerdo a un formato condicional, esta tal cual la tienes, modificando los rango, pero aun así no me funciona.
Gracias.
los colores que vez en el formato condicional no son tomados por la celda realmente es por eso que no te funciona la macro, para que l amacro funcione pinta la celda.
Hola Gustavo,
lo primero agradecer que compartas tus conocimientos puesto que son de gran ayuda.
Quería preguntarte de qué manera se puede usar el sumarcolorcelda si estoy usando formatos condicionales. Es decir, en un rango de celdas veo los números del mes en negro si son días laborables o en rojo si son días festivos. Quiero sumar solamente los festivos (rojo) y esta fórmula no los diferencia, pero claro me gustaría no tener que pintar de forma manual. Entonces la hoja pierde funcionalidad.
Si quieres te paso el archivo.
Muchas gracias de antemano.
Luis te recomiendo leer el siguiente enlace:
http://www.cpearson.com/excel/CFColors.htm
hay que hacer otra macro para determinar celdas con aplicación de formato condicional.
Hola he probado el codigo y no me funciona, se tiene que hacer alguna macro.
Gracias
Dani justamente el archivo es una macro, has habilitado macros antes de empezar a probarlo?
Hola Gustavo,
Gracias por la información ya funciona, pero ahora quiero hacer una suma y no consigo hacerlo funcionar.
necesito sumar por color y que me sume si en otra celdilla hay un 10,11,12 que serian los meses del año, he hecho esto pero no funciona, disculpa mi ignoracia.
=SUMAR.SI.CONJUNTO(G4TRIM!G5:G90;G4TRIM!B5:B90;»10″)*(G4TRIM!G100:G185;G4TRIM!Y102;G4TRIM!G5:G90)
Gracias.
recuerda que esta es una función personalizada, en tal caso quieres combinarla, no comprendo bien.
Gracias Gustavo, encuentro que es una fórmula genial.
¿Se podría hacer esta misma fórmula para celdas no adyacentes (rangos salteados) y para fórmulas matriciales?
Ahí lo dejo…
Nuri, si se podría pero tendría que hacer una nueva macro. Vamos a ver si en unas semanas hago un nuevo artículo.
Hola buen día, ¿Y se podría hacer pero que cambiara automáticamente la suma si modificamos un color?
tendría que agregar a la función application.volatile
Gustavo, la funcion application.volatile en que linea se debe de ingresar?
gracias.
colocalo al inicio.
podrias pasar los codigos con este cambio a que actualice automaticamente
Voy a darme un tiempo cualquier día para ver esto.
Hola, habras echo ese cambio, para que cambie el calculo cuando cambiemos el color de la celda automaticametne.
Gracias.
Para hacer eso se debería pogramar en los eventos de la hoja, pero no quedaría del todo bien, este tipo de fórmula no se presta para que sea muy dinámica que digamos.
Hola buenos días, gracias por el aporte, tengo un listado de 5000 cantidades, quiero sumar o restar algunas cantidades las identifico por color de fuente, al descargar su programa no me funciona que puedo hacer, cabe mencionar que si solo lo suma me puede servir.
Agradezco de antemano por el tiempo y la ayuda proporcionada.
indícame mas detalle para poder ayudarte.
Estimado muy bueno, pero aun no se como poder hacer que esa formula lo pueda aplicar en otra hoja nueva de excel, si pudieras brindarme ese dato estaré muy agradecido.
debes guardar con formato xla o xlam el archivo, luego desde las opciones de excel activarlo como complemento.
Estimado tengo una duda. Si tuviera un rango por ejemplo de «$A$1:$A$10», en donde:
A1(color negro), A2(color azul), A3, (color amarillo), A4 (color blanco), A5 (color blanco), A6 (color amarillo), A7 (color blanco), A8 (color blanco), A9 (color azul), A10 (color amarillo).
Como haría para sumar las celdas del mismo color mediante una macro, es decir: La A1 = SUMA(A2,A9) , A3 = SUMA(A4,A5) , A6 = SUMA(A7,A8) , A9 = SUMA(A10).
Johan pero utiliza lo que esta en el archivo, justamente hace lo que necesitas.
Muchas Gracias por el aporte es muy importante
Muchas gracias por el aporte.
Me soluciono el día.
Pero no se como dejar esta función predeterminada.
Hola Gustavo!
Gracias por tu ayuda, es la función necesitaba.
Quisiera que me apoyaras en lo siguiente, estoy usando excell en versión inglés y no sé como aplicar esta formula.
Podrías ayudarme por favor.
Mil gracias
Lucero
Junto lo que andaba buscando,….macro para sumar por color de la fuente,….muchas gracias. desde San Felipe, Chile
Como siempre mi estimado sebastiani ud. no deja de sorprenderme , siempre innovando, lo voy ha tomar como un ejemplo de perseverancia.
Saludos desde chorrilllos – lima
Muchas gracias !!!
Fue de gran ayuda : )