Como SUMAR y CONTAR celdas con color de fondo en Excel 2007


Uno de los problemas a los que se enfrentan los usuarios de Excel asiduamente es cuando se encuentran ante una serie de datos con el fondo de celda de un color y desean contar o sumar las celdas que tengan aplicado ese color de fondo y no el resto. Este concepto es un concepto que se estudia en los cursos avanzados. Para entender los conceptos avanzados primero has de asegurarte que conoces todas las técnicas intermedias. 
Para ello tenemos varias opciones:
  1. Crear una función personalizada
  2. Crear una macro
  3. Utilizar macros XLM

En Excel 2007 hay una manera muy sencilla de realizar esto mediante filtros y subtotales

PASOS
Supongamos que tenemos los siguientes datos coloreados en amarillo

Como solo queremos sumar los datos de las celdas que estén en amarillo, lo primero que hemos de hacer es aplicar un filtro por color de celda
  • Hacemos clic con el botón derecho en la celda que tenga el color de fondo
  • Seleccionamos FiltroFiltrar por color de la celda seleccionada


El filtro mostrará solo aquellas celdas con el color de fondo seleccionado, en nuestro caso, el amarillo
  • Nos situamos debajo de la última celda filtrada


  • Hacemos clic en el sumatorio o bien pulsamos Alt + =
Por defecto, aparecerá la función SUBTOTALES con todo el rango seleccionado
Para que Excel 2007 realice el cálculo correctamente, no hemos de seleccionar la celda que contiene el símbolo del filtro, ya que entonces incluirá todos los resultados.
  • En nuestro caso, seleccionaremos desde A2:A6 en vez de A1:A6


  • Pulsamos Intro
El resultado final mostrará la suma de las celdas con el color de fondo que hayamos asignado. En nuestro caso, la suma de las celdas con color de fondo amarillo es 58941.


En el caso de que queramos que Excel 2007 cuente el número de celdas con color de fondo en lugar de sumarlas, solo hemos de modificar el primer argumento de la función de SUBTOTALES correspondiente al número de Función.
En la imagen inferior podemos observar que el número de función correspondiente a CONTAR es el 2. Por tanto, modificamos en la función SUBTOTALES el 9 por el 2.






Si tienes alguna duda sobre este artículo, crees que hay algo incorrecto (falta de ortografía, error en la imagen), una solución más sencilla o distinta, algo no te ha quedado claro o simplemente quieres saber más sobre ésta u otras funciones o posibilidades y no quieres dejar un comentario, rellena el Formulario de Contacto de la derecha.Te contestaré personalmente.

10 comentarios:

Miguel Paucar dijo...

Saludos desde PERU:
mpaucarl@uni.pe

Mi estimado cual es la macro (en excel 2003)que pueda sumar valores de celdas que tienen un color de relleno por formato condicional.
El criterio del color de relleno, es de solo una condicion, pues cuando el valor lo encuentre en una columna de datos, entonces se pinte de color de relleno ROJO.
Espero tu pronta respuesta.
Estoy agradecido anticipadamente por tu respuesta.
Saludos desde PERU.

Atte.
Miguel Paucar.

Me gustaria enviarte mi archivo de excel 2003, pero no tengo tu direccion electronica.

Obs, Ya revise la pagina :
http://www.jrgc.es/ejemplos/sumar_o_contar_celdas_segun_color_formato_condicional.xls
pero no funciona en mi caso particular

Estare atento a tu respuesta.
Por favor , te pido tu ayuda!!!
Gracias

Aladino dijo...

Hola, Miguel

Puedes enviárnoslo haciendo clic sobre el enlace 5incoaprendices del apartado Consultas 5incoaprendices que aparece a la derecha bajo la sección Suscríbete y rellenar el formulario. Adjuntas el fichero.

No obstante, comentarte que puedes utilizar funciones genéricas o concretas, pero depende de cada caso. Envíanoslo y te responderemso a la mayor brevedad posible.

Muchas gracais por la confianza depositada.

Juan R Garces dijo...

Buen dia. ¿existe una formula (no una macro) que me cuente las celdas de un rango que tienen un color determinado? Tengo el rango de celdas de A5:A1250 y estan pintadas de 4 colores diferentes, necesito crear una formula que me cuente cuantas celdas de color amarillo hay, sin tener que filtrar, subtotalizar o crear macros, sino una formula fija que me cuente esas celdas. Muchas gracias
juanrgarces25@gmail.com

Aladino dijo...

Hola, Juan

No existe hasta la fecha ninguna función original de Excel que permita contar o sumar celdas por color, ya que marcar las celdas con colores es una mala práctica, y los desarrolladores no apoyan la prioridad del formato a la de los datos. Por eso, hay que utilizar soluciones mediante, filtros, búsquedas por formato, subtotales, códigos, complementos o funciones personalizadas. Esto es así, salvo en caso de que el color se haya aplicado mediante formato condicional.

Muchas gracias por ponerte en contacto con nosotros

Anónimo dijo...

Hola! Oye Aladino, a ver si me puedes echar un cable. Yo precisamente quiero contar el número de celdas que han sido coloreadas por un formato condicional. En este caso el formato condicional ha sido marcar las celdas con el número más grande, es decir, en reglas de formato "Aplicar formato a los valores que están en el rango en - Superior - 1".

Esto funcina sin problemas, y de cada columna me va marcando qué valor es el más elevado. Ahora quiero contar por filas cuál es la incidencia que más veces ha obtenido el valor más elevado, y no hay manera. No sé qué parámetro he de poner en el campo criterio de la función contar.si

En teoría, por lo ejemplos del excel y por lo que he visto por ahí, debería ser "1 superior", que es como se llama la condición del formato, pero nada... A ver si alguien me puede dar una idea.

Un saludo y muchas gracias ^_^

Miguel

Aladino dijo...

Gracias por tu consulta. El problema que planteas no se puede solucionar aplicando la misma función que el formato condicional, ya que el formato lo has aplicado a las columnas y, sin embargo, el cálculo lo quieres realizar por filas. por tanto, habrá que utilizar otra fórmula.

La solución pasa por utilizar varios contar.si sumados. Ejemplo:

=contar.si(rangofila;max(columna1)+contarsi(rangofila;max(columna2)) y así tantas veces como columnas tengas

Publicaremos un ejemplo en breve.

Anónimo dijo...

Vaya, yo pensé que sería mucho más sencillo. Muchas gracias aladino, estaré atento a ese ejemplo ;-)

Aladino dijo...

Ya hemos publicado la respuesta a tu consulta.

http://www.5incoaprendices.com/2010/05/contar-celdas-con-formato-condicional.html

Gracias por tu visita

Ignacio Haro dijo...

Si es posible definiendo una función en una macro que cuente las celdas de un color determinado o la suma de los valores de unas celdas de un color determinado.
Si quieres un ejemplo mandame un correo

Aprendiz dijo...

Sí, claro. Depende del resultado que desees obtener. Envíanos tu consulta con un archivo de ejemplo a través de este formulario y te atenderemos encantados.

http://www.emailmeform.com/builder/form/483011

Gracias por tu visita

Publicar un comentario

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Top WordPress Themes