Cómo contar celdas por color en Excel: tres métodos que funcionan

Imagina una hoja de Tareas donde las celdas verdes son «Hecho» y las rojas son «Pendiente». Quieres saber cuántas hay de cada sin añadir una columna de estado. En este tutorial verás que Excel no trae una función nativa para eso: ni CONTAR.SI (COUNTIF) ni CONTAR.SI.CONJUNTO (COUNTIFS) ni SUMAR.SI miran el color, solo el valor. La solución es una pequeña función VBA (una UDF, función definida por el usuario) que recorre el rango, compara el color de cada celda con el de una celda de muestra y devuelve la cuenta. Funciona en Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024 y Microsoft 365 para Windows y Mac; en Excel para la web no se ejecuta, ya que allí las macros VBA no están disponibles.

Por qué Excel no cuenta por color con una fórmula normal

Tienes una hoja de Tareas con 19 filas. En la columna Estado pintas verde las terminadas y rojo las pendientes. Cuando quieres saber cuántas llevas hechas, abres una celda y pruebas lo primero que se te ocurre: =CONTAR.SI(B2:B20;"Hecho"). El resultado es 0, porque en la columna B no pone «Hecho»: pone «hecho», «caliente», «reunión», lo que sea, y además el verde lo pusiste con el cubo de pintura, no escribiéndolo.

CONTAR.SI, CONTAR.SI.CONJUNTO y SUMAR.SI (SUMIF) trabajan con valores: números, textos, fechas, referencias a celdas. No leen el color de relleno. La ficha oficial de Color de relleno lo dice expresamente: «Pintar una celda no cambia su valor: una fórmula normal no puede leer el color (no hay SUMAR.SI por color sin VBA)». El formato condicional aplica el color según una regla, pero tampoco lo expone a las fórmulas; solo lo dibuja encima.

Por eso, cuando lo que diferencia tus datos es un color, necesitas VBA. Una UDF es una función escrita por ti que Excel aprende a usar como si fuera BUSCARV o SUMA: la escribes en una celda con =SuNombre(...) y te devuelve un número.

Qué necesitas tener listo antes de tocar VBA

• Mostrar la pestaña Programador, que viene oculta. Ve a Archivo > Opciones > Personalizar cinta de opciones y marca la casilla Programador en la columna derecha.
• Guardar el libro como Libro de Excel habilitado para macros (.xlsm), porque un .xlsx no guarda código VBA. La primera vez que pulses Guardar, Excel te preguntará si quieres mantener el formato .xlsx o usar .xlsm: elige .xlsm.
• Tener una celda de muestra por cada color que quieras contar: una pintada en verde, otra en rojo, y así. La fórmula compara el color de cada celda del rango con el de esa muestra.
• Aceptar que la UDF solo funcionará en el libro donde la crees. Si envías el archivo a alguien y su Excel no tiene habilitadas las macros, verá #¿NOMBRE? donde estaba la fórmula.

La función que cuenta celdas por color: la UDF de VBA

La idea es sencilla: recorres el rango celda a celda, lees su color de relleno con .Interior.Color y lo comparas con el color de la celda de muestra. Cada coincidencia suma uno al contador. Al final devuelves ese contador como un número entero (Long).

Function ContarPorColor(rango As Range, celdaColor As Range) As Long
    Dim celda As Range
    Dim colorMuestra As Long
    Dim contador As Long

    colorMuestra = celdaColor.Interior.Color
    For Each celda In rango
        If celda.Interior.Color = colorMuestra Then
            contador = contador + 1
        End If
    Next celda

    ContarPorColor = contador
End Function

Argumentos:

• rango: el rango que quieres recorrer, por ejemplo B2:B20.
• celdaColor: una sola celda que actúa como muestra del color a contar, por ejemplo E2.

La salida, si en B2:B20 hay 7 verdes y el resto de otros colores, es:

7

Crear la función en tu libro paso a paso

1. Abre el libro de Tareas y, si aún no lo has hecho, muestra la pestaña Programador y guarda como .xlsm.
2. Pulsa Alt+F11 para abrir el editor de Visual Basic.
3. En el menú del editor, ve a Insertar > Módulo. Aparece una ventana en blanco a la derecha.
4. Pega dentro el código de la UDF ContarPorColor que viste arriba.
5. Cierra el editor de VBA (Alt+Q o la X de la ventana). Vuelves al libro.
6. En la celda G2 escribe =ContarPorColor(B2:B20;E2) y pulsa Entrar.

Poner la celda de muestra a mano y comprobar el resultado

La celda E2 es la muestra de color. Píntala tú a mano para que la fórmula tenga algo con qué comparar.

Pasos para preparar la hoja:

1. En E2, aplica el mismo verde que usas en las celdas «Hecho». Ruta: Inicio > Fuente > Color de relleno, elige el verde.
2. En E3, aplica el rojo de las celdas «Pendiente».
3. En G2 escribe =ContarPorColor(B2:B20;E2). Resultado: 7 (las tareas en verde).
4. En G3 escribe =ContarPorColor(B2:B20;E3). Resultado: 5 (las tareas en rojo).
5. Como comprobación, en H2 escribe =G2+G3 y en I2 escribe =CONTARA(B2:B20). Ambos deben coincidir.

Y si además del color quieres cumplir otra condición

A veces no quieres contar todas las verdes, sino solo las verdes que además cumplen algo: importes mayores de 1.000, por ejemplo. Para eso se amplía la UDF con un segundo criterio.

Function ContarPorColorCond(rango As Range, celdaColor As Range, _
                            rangoCond As Range, texto As String) As Long
    Dim i As Long
    Dim colorMuestra As Long
    Dim contador As Long

    colorMuestra = celdaColor.Interior.Color
    For i = 1 To rango.Cells.Count
        If rango.Cells(i).Interior.Color = colorMuestra Then
            If Evaluate("""" & rangoCond.Cells(i).Value & """ " & texto) Then
                contador = contador + 1
            End If
        End If
    Next i

    ContarPorColorCond = contador
End Function

Sobre la hoja Ventas, donde la columna C tiene los importes:

=ContarPorColorCond(B2:B20;E2;C2:C20;">1000")

Argumento a argumento:

• B2:B20: el rango con las celdas pintadas que quieres contar.
• E2: la celda de muestra del color (verde, por ejemplo).
• C2:C20: el rango paralelo donde se evalúa la condición.
• ">1000": el criterio, escrito como texto entre comillas, igual que en CONTAR.SI.

Sumar en vez de contar, con la misma idea

Si en lugar de cuántas celdas verdes hay, quieres cuánto suman sus importes, la UDF gemela acumula el valor en vez de incrementar un contador.

Function SumarPorColor(rangoValores As Range, celdaColor As Range) As Double
    Dim celda As Range
    Dim colorMuestra As Long
    Dim total As Double

    colorMuestra = celdaColor.Interior.Color
    For Each celda In rangoValores
        If celda.Interior.Color = colorMuestra Then
            total = total + celda.Value
        End If
    Next celda

    SumarPorColor = total
End Function

En la hoja Ventas, con los importes en B2:B20:

=SumarPorColor(B2:B20;E2)

Por qué la cuenta falla o cambia sin avisar

La UDF es fiable mientras el color de la muestra y el de las celdas del rango sean exactamente el mismo. En la práctica eso se rompe por cuatro causas.

Pintar el mismo color con el cubo y con formato condicional no es el mismo color

Si pintas unas a mano y otras las pone el formato condicional, el valor numérico de .Interior.Color puede ser distinto aunque en pantalla se vean idénticas. El resultado de la UDF será más bajo de lo esperado.

Pasos para arreglarlo:

1. Borra el color de B4 desde Inicio > Fuente > Color de relleno > Sin relleno.
2. Vuelve a pintar B4 con el mismo verde que E2, usando el mismo botón Color de relleno.
3. Comprueba en G2 que ahora cuenta las tres filas verdes.

Cambiar el color de la muestra cambia todos los resultados

E2 es el corazón del método: si la pintas de amarillo, G2 pasa a contar amarillos. Eso es esperado, pero conviene saberlo y, si quieres protegerla, fijarla.

Pasos para fijar la muestra al copiar la fórmula:

1. En G2 tienes =ContarPorColor(B2:B20;E2).
2. Pulsa F4 con el cursor sobre E2 y la referencia se convierte en $E$2.
3. Copia G2 hacia abajo si la quieres repetir: la muestra no se mueve.

Abrir el libro en otro equipo sin macros pierde la función

Si abres el .xlsm en Excel para la web, en un Excel de escritorio con macros deshabilitadas, o en un equipo donde la pestaña Programador no está disponible, las celdas con la UDF mostrarán #¿NOMBRE?. La fórmula sigue ahí, pero Excel no sabe calcularla.

Pasos para salir del paso cuando necesitas el número y no puedes abrir el archivo en un Excel con macros:

1. Selecciona el rango con las celdas que dan error.
2. Copia con Ctrl+C.
3. Pega con Ctrl+Alt+V, elige Valores, y pulsa Aceptar. Los #¿NOMBRE? se sustituyen por el último número que tenían antes de abrir el libro.

Hasta dónde llega este método y cuándo conviene Power Query

La UDF vale para libros personales o compartidos en una red controlada, con rangos de tamaño medio (hasta algunas decenas de miles de celdas). Más allá de eso se ralentiza, porque VBA recorre el rango celda a celda.

Tampoco distingue matices: si tienes tres tonos de verde, tendrás que elegir una muestra y asumir que los otros dos no se cuentan. Y los patrones de relleno (rayas, puntos) no entran en .Interior.Color; solo el color sólido.

Cuando las reglas que pintan son estables y conocidas, hay una alternativa oficial: añade una columna auxiliar que devuelva el resultado del formato condicional con la función = directa sobre la regla (por ejemplo =SI(B2>1000;"Alto";"Bajo")) y usa CONTAR.SI sobre esa columna. Si los datos vienen de otro sistema y vas a recargarlos a menudo, Power Query (disponible desde Excel 2016) puede agrupar y contar por la condición que genera el color, sin macros y sin UDF.

Lo que ya sabes hacer con colores y tu siguiente paso

• No existe una función nativa de Excel para contar por color: CONTAR.SI y compañía miran valores, no rellenos.
• La solución es una UDF de VBA que compara .Interior.Color con una celda de muestra, y requiere la pestaña Programador y guardar el libro como .xlsm.
• La celda de muestra es la clave: la fórmula cuenta exactamente lo que esa celda tiene pintado.
• La UDF vive solo en el libro donde se crea. En Excel para la web, en móviles o en equipos sin macros habilitados, se muestra #¿NOMBRE?.
• Si arrastras la fórmula a otras celdas, fija la referencia a la muestra con F4 para que no se desplace.

Tu siguiente paso: practica con la tecla F4 sobre una referencia como E2 hasta convertirla en $E$2. Es el mismo gesto que usarás mañana con BUSCARV, SUMAR.SI.CONJUNTO o cualquier fórmula que tenga una constante que no debe moverse.

Dudas al contar celdas por color en Excel