Sumar celdas por texto y color de fondo

Solo consultas sobre Funciones y Fórmulas Excel.
Reglas del Foro
1. Antes de hacer tu pregunta intenta con el buscador de este foro (muchas preguntas ya fueron respondidas antes!)
2. Si haces una nueva pregunta, es muy recomendable que adjuntes el ejemplo Excel para poder comprenderla mejor!
3. Realiza tu pregunta de forma clara, explicando bien cada paso de lo que haces y tendrás más probabilidad de respuesta!
Responder
kine_house
Miembro Frecuente
Miembro Frecuente
Mensajes: 25
Registrado: 16 Oct 2012 16:26

Sumar celdas por texto y color de fondo

Mensaje por kine_house » 02 Ene 2014 03:12

Estimados.
Les escribo para solicitar su ayuda.
Tengo un libro excel (que se los envio como adjunto) en el cual, en la Hoja 1, hay tablas simples que se repiten, las que representan cada dia de la semana, con fecha. Y en la Hoja 2, hay un resumen de cada sesion realizada.
La idea es que al anotar un nombre en la Hoja 1, varias veces (correspondiente al total de las sesiones que debo ver a esa persona y que en general son 10), anote ese nombre en la Pagina 2, y solo sean contadas las veces en que ese nombre especifico es coloreado. Siempre ocupo el mismo color, sin importar el nombre que coloree.
Como podran ver en el ejemplo, anote el nombre ALEXIS 10 veces, y en la pagina 2 aparece la formula que he creado que permite contar ese nombre dentro de toda la hoja 1. Sin embargo, el nombre ALEXIS esta coloreado solo 3 veces en la pagina 1, y necesito que en la Hoja 2 solo aparezcan esas tres veces.
Cabe destacar que en la Hoja 1 anotare muchos nombres, los cuales traspasaré a la Hoja 2 de manera manual (a menos que exista alguna forma que agregue de forma automatica los nombres anotados en la Hoja 1 a la Hoja 2).
Intenté agregando un modulo con la funcion SUMAR COLOR, pero no era lo que necesitaba, y no he podido encontrar solucion.
En pocas palabras, y despues de tanta verborrea, necesito que la Hoja 2, la columna REALIZADAS haga un conteo tomando en cuenta dos condiciones, texto y color de fondo de la celda.
Podrian ayudarme en ello?
Les agradezco de antemano.
Saludos
No tienes los permisos requeridos para ver los archivos adjuntos a este mensaje.



kine_house
Miembro Frecuente
Miembro Frecuente
Mensajes: 25
Registrado: 16 Oct 2012 16:26

Re: Sumar celdas por texto y color de fondo

Mensaje por kine_house » 02 Ene 2014 03:20

Se me olvidaba un detalle, y es que el conteo pyeda ser en tiempo real, sin tener que usar algun boton, tal como funciona la formula que agregué, que busca el nombre en la Hoja 1 y lo cuenta de inmediato. Sera posible?
Saludos



zebenzui
Miembro Frecuente
Miembro Frecuente
Mensajes: 106
Registrado: 29 Abr 2013 13:50

Re: Sumar celdas por texto y color de fondo

Mensaje por zebenzui » 02 Ene 2014 05:56

Se puede, solo que hay un inconveniente. Excel recalcula de forma automtica cuando detecta un cambio en los valores o formulas de las celdas, pero no cuando cambias un color. En las opciones de excel no existe la posibilidad de recalcular por ese tipo de cambios, asi que, a no ser que alguien aporte una solucion, el recalculo se hara solo cuando se cambien datos o lo hagas de forma manual.

en cuanto a la funcion para contar, abre el editor de VB, crea un modulo nuevo y agrega esta funcion:

Código: Seleccionar todo

Function CONTAR_SI_TEXTO_COLOR(rango As Range, texto As String, color As Integer) As Integer
'si texto es una cadena vacia no se tiene en cuenta
Dim contar As Integer
contar = 0 'para contar cuantas celdas cumplen las condiciones
For Each celda In rango  'para cada celda contenida en el rango
      'si el texto es el correcto o esta vacio y el color coincide, contamos uno
      If ((UCase(celda.Value) = UCase(texto)) Or (texto = "")) And (celda.Interior.colorIndex = color) Then
         contar = contar + 1
      End If
Next
contar_si_texto_color = contar 'retornar el valor

End Function
La funcion devuelve la cuenta de cuantas celdas hay en el rango indicado, con el texto indicado (trata mayusculas y minusculas por igual) y el color indicado. Si el texto esta vacio "" solo cuenta las celdas con el color de fondo indicado sin tener en cuenta si tiene algo escrito o no

por ejemplo
=CONTAR_SI_TEXTO_COLOR(B1:D10; "Hola";1)
esto contara en el rango b1:d10 cuantas celdas tienen fondo negro y la palabra hola

o esto:

=CONTAR_SI_TEXTO(B1:D10;"";1)
contará cuantas celdas tienen fondo negro (da igual que tiene escrito)

en cuanto al color, el numero utilizado es el colorIndex. Cada color tiene un numero, por lo que debes saber que color corresponde a que numero. Para esto si quieres te paso esta funcion:

Código: Seleccionar todo

 Function colorIndex(c As Range) As Integer
colorIndex = c.Interior.colorIndex
End Function
que te permite usarla para detectar el color que quieras de una celda, bien manualmente (agrega la funcion al modulo, luego en la hoja pon una celda, p. ej. A1, con el color que necesites y en otra celda escribes =colorIndex(A1) y veras el numero.

o tambien la puedes usar poniendo un color de fondo en una celda cualquiera, por ejemplo A1, y la usas en la otra funcion para obtener el colorIndex

=CONTAR_SI_TEXTO(B1:D10;"Hola"; colorIndex(A1))

Esto contará cuantas celdas hay en el rango b1:d1 con el texto Hola y el mismo color de fondo que A1

Recuerda que si cambias solo los colores, excel no recalcula de forma automatica, debes cambiar algun dato o aplicar un recalculo manual.

Saludos.



kine_house
Miembro Frecuente
Miembro Frecuente
Mensajes: 25
Registrado: 16 Oct 2012 16:26

Re: Sumar celdas por texto y color de fondo

Mensaje por kine_house » 02 Ene 2014 23:42

Estimado zebenzui

Me sirvio mucho tu ayuda, si bien, y como lo explicaste, no actualiza automaticamente las formulas, he creado una pequeña macro que permite actualizar todas las formulas de la Hoja 2, asociada a un boton. Asi cada vez que desee saber el numero total de sesiones, entro a esa hoja y hago click en el boton para actualizar.

Muchisimas gracias por darte el tiempo de responder mi consulta.

Saludos desde San Antonio, Chile

P.D.: Gracias a la ayuda de zebenzui, pido a los administradores que cierren mi consulta. :D



zebenzui
Miembro Frecuente
Miembro Frecuente
Mensajes: 106
Registrado: 29 Abr 2013 13:50

Re: Sumar celdas por texto y color de fondo

Mensaje por zebenzui » 03 Ene 2014 06:37

Me alegra que te haya servido.

Saludos.



eduy2k
Miembro Nuevo
Miembro Nuevo
Mensajes: 1
Registrado: 17 Feb 2014 12:09

Re: Sumar celdas por texto y color de fondo

Mensaje por eduy2k » 01 Oct 2014 10:30

Muchas Gracias por esta Macro, me sirvió muchísimo, y me ahorro muchos dolores de cabeza.



Jgarciaort
Miembro Frecuente
Miembro Frecuente
Mensajes: 28
Registrado: 02 Feb 2016 21:03

Re: Sumar celdas por texto y color de fondo

Mensaje por Jgarciaort » 15 Abr 2016 20:51

Hola,
he estado usando esta macro sin problemas durante mucho tiempo. Pero ayer creé un nuevo archivo y ahora me da como error #¡valor!. Por más vueltas que le doy no consigo entender porqué no me funciona en este nuevo archivo.
Lo subo a ver si me podéis ayudar.

Muchas gracias
Saludos
No tienes los permisos requeridos para ver los archivos adjuntos a este mensaje.



GJ
Miembro Frecuente
Miembro Frecuente
Mensajes: 12
Registrado: 15 Jul 2019 09:47

Re: Sumar celdas por texto y color de fondo

Mensaje por GJ » 16 Jul 2019 05:07

HOLA!
He probado esta macro con un archivo que me rellena el color de las celdas con un formato condicional y no me funciona. Creo que es por el número del color; el colorIndex para los colores rellenados con formato condicional creo que no funciona.
¿Alguien me podría ayudar?
¡Gracias!



Responder