提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何根据Google表格中的单元格颜色对单元格进行计数或求和?

根据特定的单元格背景颜色对单元格值进行计数或求和,以得到如下所示截图的结果。 本文将讨论如何在Google工作表和Microsoft Excel中解决此任务。

使用Google工作表中的脚本根据单元格颜色计算单元格值

使用Google工作表中的脚本,根据单元格颜色求和单元格值

使用Microsoft Excel中的Kutools for Excel对单元格颜色的单元格值进行计数或求和


基于Excel中的背景,字体,条件格式颜色的计数/求和单元格值:

Kutools for Excel's 按颜色计数 功能可以帮助您根据Excel工作表中的需要,根据字体,背景或条件格式颜色进行一些计算。

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


使用Google工作表中的脚本根据单元格颜色计算单元格值


以下脚本可帮助您根据特定单元格颜色对单元格值进行计数,请按以下方法进行:

1。 点击 > 脚本编辑器,看截图:

2。 在打开的项目窗口中,单击 文件 > 最新上市 > 脚本文件 打开一个代码窗口,看截图:

3。 在提示框中,请输入此脚本代码的名称,请参阅屏幕截图:

4。 点击 OK 然后复制并粘贴以下代码以将原始代码替换为代码模块,请参阅截图:

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。 然后保存此脚本代码,并返回表单,输入此公式: = countcoloredcells(A1:E11,A1) 放入空白单元格中,然后按 输入 键来获得计算结果。 看截图:

注意:在这个公式中: A1:E11 是您要使用的数据范围, A1 是要填充特定颜色的单元格。

6。 重复上述公式来计算其他特定的彩色单元格。


使用Google工作表中的脚本,根据单元格颜色求和单元格值

要将单元格值与特定单元格颜色相加,请在脚本代码下应用。

1. 点击 > 脚本编辑器 去项目窗口,然后点击 文件 > 最新上市 > 脚本文件 要插入另一个新的代码模块,请在提示框中键入此脚本的名称,请参阅屏幕截图:

2。 点击 OK 并在打开的代码模块中复制并粘贴下面的脚本代码以替换原始代码,参见截图:

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。 然后保存此代码,返回到工作表并输入以下公式: = sumcoloredcells(A1:E11,A1) 放入空白单元格中,然后按 输入 键来获得计算结果,请参阅截图:

注意:在这个公式中: A1:E11 是您要使用的数据范围, A1 是具有您想要求和的特定背景颜色的单元格。

4。 然后你可以重复上述公式来总结其他特定的彩色单元格。


使用Microsoft Excel中的Kutools for Excel对单元格颜色的单元格值进行计数或求和

在Microsoft Excel中,要根据特定单元格颜色对单元格值进行计数或求和, Kutools for Excel 按颜色计数 实用程序可以帮助您尽快完成此任务。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

安装后 Kutools for Excel,请这样做:

1。 根据单元格颜色选择要计算或求和的范围,然后单击 企业服务 > 按颜色计数,看截图:

2。 在 按颜色计数 对话框中选择 标准格式 来自 色彩方法 下拉列表,然后选择 背景 来自 计数类型 下拉,看截图:

3。 然后点击 生成报告 按钮,并立即生成带有计算结果的新工作表,请参阅截图:

注意:通过这个强大的功能,您还可以根据条件格式或字体颜色计算单元格值。

点击下载并立即免费试用Kutools for Excel!


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
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.
    La · 5 days ago
    TypeError: Não é possível chamar o método "pop" de null. (linha 5, arquivo "sumbycolor")

    How can I fix this error? Thank you
  • To post as a guest, your comment is unpublished.
    Nataly · 1 months 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 · 3 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 · 3 months ago
    Thank you so much, truly useful!
  • To post as a guest, your comment is unpublished.
    Roberto · 3 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 · 7 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 · 7 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 · 8 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 · 8 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 · 8 months ago
    thank you :*
  • To post as a guest, your comment is unpublished.
    Pedro Johnston · 8 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 · 10 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 · 10 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 · 10 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 · 10 months ago
    Thanks, this worked great!
  • To post as a guest, your comment is unpublished.
    Nikolaj Justin Kinas · 1 years 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 · 1 years 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 · 1 years 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 · 1 years 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.