Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo contar o sumar celdas basadas en el color de celda en la hoja de Google?

Contar o sumar valores de celda en función del color de fondo de celda específico para obtener el resultado como se muestra a continuación. Este artículo, voy a hablar sobre cómo resolver esta tarea en la hoja de Google y Microsoft Excel.

Contar los valores de celda en función del color de la celda con la secuencia de comandos en la hoja de Google

Sumar valores de celda basados ​​en el color de la celda con la secuencia de comandos en la hoja de Google

Contar o sumar valores de celda en el color de la celda con Kutools para Excel en Microsoft Excel


Valores de recuento / suma de celda basados ​​en el fondo, fuente, color de formato condicional en Excel:

Kutools for Excel's Cuenta por color la función puede ayudarlo a obtener algunos cálculos en función de la fuente, el fondo o el color del formato condicional que necesite en la hoja de cálculo de Excel.

Kutools for Excel: con más de 200 útiles complementos de Excel, de prueba sin límite en días 60. Descargar y prueba gratis ahora!


Contar los valores de celda en función del color de la celda con la secuencia de comandos en la hoja de Google


La siguiente secuencia de comandos puede ayudarlo a contar los valores de celda en función del color de celda específico, haga lo siguiente:

1. Hacer clic Herramientas > Editor de scripts, mira la captura de pantalla:

2. En la ventana del proyecto abierto, haga clic Envíe el > Nueva > Archivo de comandos para abrir una ventana de código, ver captura de pantalla:

3. Y en el cuadro de diálogo, ingrese un nombre para este código de script, vea la captura de pantalla:

4. Hacer clic OK y luego copie y pegue el siguiente código para reemplazar el código original en el módulo de código, vea la captura de pantalla:

function countColoredCells(countRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var countCells = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        countCells = countCells + 1;
  return countCells;
};

5. A continuación, guarde este código de secuencia de comandos y vuelva a la hoja, ingrese esta fórmula: = countcoloredcells (A1: E11, A1) en una celda en blanco, y luego presione entrar clave para obtener el resultado calculado. Ver captura de pantalla:

Nota: En esta fórmula: A1: E11 es el rango de datos que quieres usar, A1 es la celda llena de color específico que desea contar.

6. Repita la fórmula anterior para contar otras células de color específicas.


Sumar valores de celda basados ​​en el color de la celda con la secuencia de comandos en la hoja de Google

Para sumar los valores de celda con un color de celda específico, aplique el código de script siguiente.

1. Haz click Herramientas > Editor de scripts para ir a la ventana del proyecto y hacer clic Envíe el > Nueva > Archivo de comandos para insertar otro nuevo módulo de código, entonces, en el cuadro de diálogo, escriba un nombre para este script, vea la captura de pantalla:

2. Hacer clic OK y en el módulo de código abierto, copie y pegue debajo del código del script para reemplazar el código original, vea la captura de pantalla:

function sumColoredCells(sumRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var sumValues = activeSht.getRange(countRangeAddress).getValues();  
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var totalValue = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        if ((typeof sumValues[i][k]) == 'number')
          totalValue = totalValue + (sumValues[i][k]);
  return totalValue;
};

3. Y luego guarde este código, regrese a la hoja e ingrese esta fórmula: = sumcoloredcells (A1: E11, A1) en una celda en blanco, y presione entrar clave para obtener el resultado calculado, ver captura de pantalla:

Nota: En esta fórmula: A1: E11 es el rango de datos que quieres usar, A1 es la celda con un color de fondo específico que desea sumar.

4. Y luego puede repetir la fórmula anterior para sumar otras células de color específicas.


Contar o sumar valores de celda en el color de la celda con Kutools para Excel en Microsoft Excel

En Microsoft Excel, para contar o sumar los valores de celda en función del color de celda específico, Kutools for Excel, Cuenta por color la utilidad puede ayudarte a terminar esta tarea lo más rápido que puedas.

Kutools for Excel : con más de 300 útiles complementos de Excel, de prueba sin límite en días 60.

Después de instalar Kutools for Excel, haz lo siguiente:

1. Seleccione las celdas para el rango que desea contar o sumar en función del color de la celda, y luego haga clic Empresa > Cuenta por color, mira la captura de pantalla:

2. En la Cuenta por color cuadro de diálogo, elija Formato estándar del Método de color lista desplegable, y luego seleccionar Antecedentes del Tipo de conteo desplegable, ver captura de pantalla:

3. Luego haga clic Generar informe y se genera una nueva hoja de cálculo con los resultados calculados a la vez, vea la captura de pantalla:

Nota: Con esta poderosa función, también puede calcular los valores de celda en función del formato condicional o el color de fuente.

¡Haz clic en Descargar y prueba gratuita Kutools para Excel ahora!


Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Funciona con Office 2007-2019 y 365, y es compatible con todos los idiomas. Es fácil de implementar en su empresa. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Nataly · 25 days ago
    Hola! Muchas gracias por tu artículo, ha sido de mucha ayuda, he logrado corregir el tema del formato regional de google cambiando . por ;
    Lo que no consigo hacer es que se actualice la formula cuando ingreso nuevos datos. ¿Sabes cómo hacer esto?
    Gracias
  • To post as a guest, your comment is unpublished.
    Kevin · 2 months ago
    Hi everyone. I use this sumColoredCells function but it will NOT auto refresh if you change cell colors. The only way it refreshes is to change a value in the range. A work-around I found was to create a checkbox somewhere to add 1 (or use insignificant fractional (.000001) if you are rounding) to a number in the range, and unchecking will add 0. This will act as a refresh "toggle" switch. Not really ideal, I know... but this is for my personal use and only a minor hassle.
  • To post as a guest, your comment is unpublished.
    Francisco · 2 months ago
    Thank you so much, truly useful!
  • To post as a guest, your comment is unpublished.
    Roberto · 2 months ago
    There were some errors on the code, like the range not being used at all and that the calculation was only made when the parameters were changed. With this version the calculation will be updated when there is an update on the sheet and the range has to be passed as a string, also some dummy cell has to be used to save a random value, you can put the color of the text just like the background to make it invisible:

    =countColoredCells("B3:B145","B1",$A$1)

    function countColoredCells(countRange, colorRef, unUsed) {
    var backGrounds = SpreadsheetApp.getActiveSheet().getRange(countRange).getBackgrounds();
    var backGround = SpreadsheetApp.getActiveSheet().getRange(colorRef).getBackground();
    var countCells = 0;
    for (var i = 0; i < backGrounds.length; ++i)
    for (var k = 0; k < backGrounds[i].length; ++k)
    if ( backGrounds[i][k] == backGround )
    ++countCells;
    return countCells;
    }

    function onEdit(e)
    {
    SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
    }
  • To post as a guest, your comment is unpublished.
    Marco · 6 months ago
    A quick solution but not the best is to add a new parameter and never use it, something like this:

    function sumColoredCells(sumRange,colorRef,test)


    The test parameter is never used, this issue here is that the values only will be refresed if first of all you set the color and then change the value.
  • To post as a guest, your comment is unpublished.
    Adam · 6 months ago
    This script used to work great, but it doesn't work anymore. Is there a change that brought it down?
  • To post as a guest, your comment is unpublished.
    Steve · 7 months ago
    Internet is full of instances of this solution and it doesn't work.
  • To post as a guest, your comment is unpublished.
    Marco · 7 months ago
    Hi there,
    on Google sheets, seems not to be working, I went on debug and for both processes, in row 5 seems to be a type error, the message is: impossible to recall the "pop2 method of null. Does it makes sense? Thanks a lot!
  • To post as a guest, your comment is unpublished.
    desi · 7 months ago
    thank you :*
  • To post as a guest, your comment is unpublished.
    Pedro Johnston · 7 months ago
    Hello,


    This function works, however, when i try to sum 2 instances of it in the same cell as in (in my case):


    =sumColoredCells(A103:AW103, C171) + sumColoredCells(A138:AW138, C171)


    It gives me an #Error - Range not found (line 6).


    It doesn't work even if i use SUM() or ADD(). Any ideas?


    Thanks in advance.
  • To post as a guest, your comment is unpublished.
    Geoff · 9 months ago
    The SumColoredCells Script works great. Do you know if there is a way to display (in a single cell) the sum of 2 colors? For example, if I wanted the sum of both the Yellow and Green Cells to display in a single cell, is that possible? I would think the formula would be:
    =SUM(sumcoloredcells(A1:E11,A1)),(sumcoloredcells(A1:E11,A5)) ....But that results in an ERROR. Any ideas?
    • To post as a guest, your comment is unpublished.
      skyyang · 9 months ago
      Hello, Geoff,
      May be there is no direct formula to solve your problem, but, you can sum the colored cells separately first, and then, sum the two calculated results as you need.
      • To post as a guest, your comment is unpublished.
        Geoff · 9 months ago
        Thanks for the reply. I have done that, but would love to consolidate the formula into a single cell, as I have to update the ranges every week. Anyway, thanks for checking!
  • To post as a guest, your comment is unpublished.
    jchew · 9 months ago
    Thanks, this worked great!
  • To post as a guest, your comment is unpublished.
    Nikolaj Justin Kinas · 11 months ago
    Hello,
    The formula does not work for me.
    If I do this in spreadsheet only errors will occur.
  • To post as a guest, your comment is unpublished.
    Danison Rarama · 11 months ago
    im trying to do something like this
    =countColoredCells (H62:H,B2)countif("MBA13R15")
  • To post as a guest, your comment is unpublished.
    Danison Rarama · 11 months ago
    Hi there,


    Can I need some help with a similar formula to SUM total of number keywords matching and reflecting the number of colors.


    What I what to achieve it determine the number of laptops which is a keyword but recognise the Available based on color of the cells?
  • To post as a guest, your comment is unpublished.
    Raul · 1 years ago
    Replace this lines:
    var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();


    var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

    Notice than , was changed for ;
  • To post as a guest, your comment is unpublished.
    Sam · 1 years ago
    does it auto update when new data is added?
  • To post as a guest, your comment is unpublished.
    Terry · 1 years ago
    I keep getting an error "Action not allowed (Line 0)". What does this mean?
  • To post as a guest, your comment is unpublished.
    Terry · 1 years ago
    I'm getting "Action Not Allowed Line 0" What am I doing wrong?
  • To post as a guest, your comment is unpublished.
    KydKat · 1 years ago
    Is there a way to include negative numbers in the mix and get the correct sum? Mine keeps adding all the numbers in the range, even if the number has a negative sign on it.
  • To post as a guest, your comment is unpublished.
    Laura · 1 years ago
    Hey, thanks for this script. I'm having a problem in the script for google sheets: TypeError: Cannot call method "pop" of null. (line 5, file "SumByColor"). Can you please help?, thanks
    • To post as a guest, your comment is unpublished.
      Patodox · 11 months ago
      The problem maybe is your regional configuration, this script thinks you are separating parameters by ',', but it is not your case probably. In my case, I use ';', so I modified the script, changing the character in the lines

      var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();

      var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();
    • To post as a guest, your comment is unpublished.
      Robinson Tapia · 1 years ago
      Hello, I have the same problem, have you fix it?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Laura,
      The script works well in my google sheets.
      If there is problem in your google sheet, I can share my google sheet with you.
      Please give your email address.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Dave Gorman · 1 years ago
    Your sumColoredCells() function is incorrect as it doesn't use sumRange
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Dave,
      The above script works well in my Google sheet.
      When applying the formula, you should save the script window first.
      Please try it, Thank you!
  • To post as a guest, your comment is unpublished.
    Todd · 1 years ago
    Thanks for the help with "Count cells based on color for google sheets". Limitation seems to be that when cell colors are changed the sheet does not refresh and totals are incorrect. I have to delete the code in the total cell and copy again from neighbour cell. Not ideal, but it works. Thanks again.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Todd,
      Yes, as you said, you should retype the formula when the cell color changes.
      May be there is no other good ways to solve this problem.
      If any other has good methods, please comment here.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Jose · 1 years ago
    Hey, thanks for this script. I'm having a problem in the script for google sheets: TypeError: Cannot call method "pop" of null. (line 5, file "SumByColor"). Can you please help?, thanks
  • To post as a guest, your comment is unpublished.
    Sheldon · 1 years ago
    Hi! Great script but I run into a problem when trying to edit the values within the coloured cells. The script does not refresh the values if I change the colour of the cell. I can only force it to refresh if i delete the contents of the cell, change the colour and then enter the value again.