Googleスプレッドシートのセルの色に基づいてセルをカウントまたは合計するにはどうすればよいですか？

次のスクリーンショットに示すように、特定のセルの背景色に基づいてセル値をカウントまたは合計して、結果を取得します。この記事では、GoogleスプレッドシートとMicrosoftExcelでこのタスクを解決する方法について説明します。

Googleスプレッドシートのスクリプトを使用して、セルの色に基づいてセル値をカウントします

Googleスプレッドシートのスクリプトを使用して、セルの色に基づいてセルの値を合計します

MicrosoftExcelのKutoolsfor Excelを使用して、セルの色のセル値をカウントまたは合計します。

Googleスプレッドシートのスクリプトを使用して、セルの色に基づいてセル値をカウントします

次のスクリプトは、特定のセルの色に基づいてセル値をカウントするのに役立ちます。次のようにしてください。

1に設定します。 OK をクリックします。 BHツール > スクリプトエディタ、スクリーンショットを参照してください：

2。開いたプロジェクトウィンドウで、をクリックします フィレット > New > スクリプトファイル コードウィンドウを開くには、スクリーンショットを参照してください。

3。プロンプトボックスに、このスクリプトコードの名前を入力してください。スクリーンショットを参照してください。

4に設定します。 OK をクリックします。 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. ナビゲーションヘッダーの BHツール > スクリプトエディタ プロジェクトウィンドウに移動し、をクリックします フィレット > New > スクリプトファイル 別の新しいコードモジュールを挿入するには、プロンプトボックスに、このスクリプトの名前を入力してください。スクリーンショットを参照してください。

2に設定します。 OK をクリックします。 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。次に、上記の数式を繰り返して、他の特定の色のセルを合計できます。

MicrosoftExcelのKutoolsfor Excelを使用して、セルの色のセル値をカウントまたは合計します。

Microsoft Excelで、特定のセルの色に基づいてセル値をカウントまたは合計するには、 Kutools for Excelさん 色で数える ユーティリティは、このタスクをできるだけ早く完了するのに役立ちます。

Kutools for Excel : 300以上の便利なExcelアドインがあり、30日以内に制限なしで無料で試すことができます.

ダウンロードに移動
30日間の無料トライアル購入
ペイパル/マイコマース

インストールした後 Kutools for Excel、次のようにしてください。

1。セルの色に基づいてカウントまたは合計する範囲のセルを選択し、[ クツールズプラス > 色で数える、スクリーンショットを参照してください：

2。の中に 色で数える ダイアログボックスで、 標準フォーマット から カラー方式 ドロップダウンリストを選択し、背景から カウントタイプ ドロップダウン、スクリーンショットを参照してください：

3。次に、をクリックします レポートを生成する ボタンをクリックすると、計算結果を含む新しいワークシートが一度に生成されます。スクリーンショットを参照してください。

注意：この強力な機能を使用すると、条件付き書式またはフォントの色に基づいてセル値を計算することもできます。

今すぐExcel用のダウンロードと無料トライアルKutoolsをクリックしてください！

最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80％向上させます

再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。セルを暗号化するパスワード付き。メーリングリストを作成するそしてメールを送る...
スーパーフォーミュラバー（複数行のテキストと数式を簡単に編集できます）; 読書レイアウト（多数のセルを簡単に読み取って編集する）; フィルター範囲に貼り付け...
セル/行/列をマージするデータを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。範囲を比較する...
[複製]または[一意]を選択します行; 空白行を選択（すべてのセルは空です）; スーパーファインドとファジーファインド多くのワークブックで; ランダム選択...
正確なコピー数式参照を変更せずに複数のセル。参照の自動作成複数のシートに; 箇条書きを挿入、チェックボックスなど...
テキストを抽出、テキストの追加、位置による削除、スペースを削除する; ページング小計の作成と印刷。セルの内容とコメントを変換する...
スーパーフィルター（フィルタースキームを保存して他のシートに適用します）; 高度な並べ替え月/週/日、頻度など。特殊フィルター太字、斜体...
ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
300以上の強力な機能。 Office / Excel2007-2019および365をサポートします。すべての言語をサポートします。企業や組織に簡単に導入できます。全機能30日間の無料トライアル。 60日間の返金保証。

続きを読みます... 無料ダウンロード... 購入...

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作業をはるかに簡単にします

Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
生産性が50％向上し、毎日何百ものマウスクリックが減ります。

続きを読みます... 無料ダウンロード... 購入...

55 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

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.
Anna · 6 days ago
Works for code:
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());
}

but You neet do call the function like that:
=countColoredCells("C6:W21";"E2";$A$1)

where
"C6-W21" is the area analysed
"E2" is the model cell
$A$1 - don't know why this is, but it writes a random number to the cell A1
- Reply
To post as a guest, your comment is unpublished.
chan · 2 months ago
thanks, :)
- Reply
To post as a guest, your comment is unpublished.
Hawjin · 4 months ago
Autoupdate of the formula

A tip is to create a script that inserts a row in the range and then removes the same row again. And then set up a time-based trigger for the function =)
- Reply
To post as a guest, your comment is unpublished.
guest · 4 months ago
it errors for me :/
- Reply
To post as a guest, your comment is unpublished.
Davide · 10 months ago
Thank you so much for these scripts, Truly appreciated, it's not to get something that works out of the box.

Question how would I go about modifying the values sum script to pull part of a cell, for exammple a cell that contains the following:
909 & 1909
{1 of 2}
SM & M (3.5)

How would I go about pulling the "3.5" in parenthesis and sum that numerical value coming out multiple cells of the same color. I know how to pull the value out in google sheets but can't figure out how to combine it with the cell color script.

Thank you.
- Reply
To post as a guest, your comment is unpublished.
Richard · 11 months ago
Has anyone updated this for the Sum colored cells? i'm ok with copying and pasting the forumla for now but it would be nice if i highlighted more cells, it would add those cells to the total automatically. I'm a newb when it comes to reading code, i tried a few things based on all the comments but alas, i'm still new.

Thanks,
- Reply
To post as a guest, your comment is unpublished.
rodrigosromaro@gmail.com · 1 years ago
PLEASE SOMEONE COULD HELP ME ?
I WAS LOOKING FOR HOW TO SOLVE THAT A MONTH AT LEAST. I TRIED MANY THINGS, CHANGED COMMAS .... AND NOTHING SOLVE THAT...

------------- TypeError: Cannot call method "pop" of null. (line 5, file "SumByColor") -----------------

//*ORIGINAL - var countRangeAddress = activeformula.match(/$(.*)\,/).pop().trim();
//*I TRYING - var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();
//*ORIGINAL - var colorRefAddress = activeformula.match(/\,(.*)$/).pop().trim();
//*I TRYING - var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

some charitable soul please help me, I don't have much programming ... #sad
- Reply
To post as a guest, your comment is unpublished.
Andrew · 1 years ago
HI, Brian! Thank's for your effort to give us the updated code. The auto update feature doesn't works for me. It doesn't write a new random number in A1 automatically, s i have to do it manually to get refresh. Is it as planed or Do i miss something? Thanks for your reply.
- Reply
- To post as a guest, your comment is unpublished.
  Milan · 5 months ago
  
  I have the same proble. Am I missing anything?
  
  Reply
To post as a guest, your comment is unpublished.
netman · 1 years ago
hi Brian, I have tried to use the updated version. It is improved but still not always update after change color.
is it possible to add a command to refresh the 'A1' value timely. thanks.
- Reply
To post as a guest, your comment is unpublished.
Brian · 1 years ago
Hi all, I wanted to give you an updated version of how to count the number of cells that are a given color. This version solves a couple of issues:

1. The count not updating when the doc changes and
2. Errors from adding another parameter to the function

// Unsed third argument
function countColoredCells(countRange,colorRef,unUsed) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
// The regex matches the parentheses as an array, gets the arguments as a string,
// then splits the arguments on the comma into another array
var arrayOfArguments = activeformula.match(/$(.*)$/).pop().trim().split(',');
// Get the first argument which is the range
var countRangeAddress = arrayOfArguments[0];
// Get the second argument, which is the reference color
var colorRefAddress = arrayOfArguments[1];
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
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;
};

// Writes a random number to A1
function onEdit(e)
{
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}

Usage: =countColoredCells(B2:E22,A28,$A$1)
- Reply
- To post as a guest, your comment is unpublished.
  Lenore · 1 years ago
  
  Do we paste all of this to replace the original code?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  skyyang · 1 years ago
  
  Hello, Lenore,
  The Brian's code is worked well to auto update the result when you change the cell color, please apply it.
  Yes, you should pasting all of the above code to replace the original one.
  
  Reply
To post as a guest, your comment is unpublished.
La · 1 years 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
- Reply
To post as a guest, your comment is unpublished.
Nataly · 1 years 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
- Reply
To post as a guest, your comment is unpublished.
coolkev99 · 2 years 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.
- Reply
- To post as a guest, your comment is unpublished.
  sudarshan · 1 years ago
  
  Hi Kevin,
  
  Hope you are keeping well. I have been facing the same problem, Can you please share how to create checkbox to add 0000.1 or so. my email id is sudarshan1210@gmail.com. Looking forward to your help.
  
  Reply
- To post as a guest, your comment is unpublished.
  Yara · 1 years ago
  
  I have the same issue and don't know how to fix it
  
  Reply
To post as a guest, your comment is unpublished.
Francisco · 2 years ago
Thank you so much, truly useful!
- Reply
To post as a guest, your comment is unpublished.
Roberto · 2 years 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());
}
- Reply
To post as a guest, your comment is unpublished.
Marco · 2 years 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.
- Reply
To post as a guest, your comment is unpublished.
Adam · 2 years ago
This script used to work great, but it doesn't work anymore. Is there a change that brought it down?
- Reply
To post as a guest, your comment is unpublished.
Steve · 2 years ago
Internet is full of instances of this solution and it doesn't work.
- Reply
To post as a guest, your comment is unpublished.
Marco · 2 years 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!
- Reply
To post as a guest, your comment is unpublished.
desi · 2 years ago
thank you :*
- Reply
To post as a guest, your comment is unpublished.
pedro.plj@gmail.com · 2 years 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.
- Reply
To post as a guest, your comment is unpublished.
Geoff · 2 years 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?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years 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.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Geoff · 2 years 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!
  
  Reply
To post as a guest, your comment is unpublished.
jchew · 2 years ago
Thanks, this worked great!
- Reply
To post as a guest, your comment is unpublished.
justinbaden9@gmail.com · 2 years ago
Hello,
The formula does not work for me.
If I do this in spreadsheet only errors will occur.
- Reply
To post as a guest, your comment is unpublished.
danison@bench.co · 2 years ago
im trying to do something like this
=countColoredCells (H62:H,B2)countif("MBA13R15")
- Reply
To post as a guest, your comment is unpublished.
danison@bench.co · 2 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?
- Reply
To post as a guest, your comment is unpublished.
Raul · 2 years ago
Replace this lines:
var countRangeAddress = activeformula.match(/$(.*)\;/).pop().trim();

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

Notice than , was changed for ;
- Reply
To post as a guest, your comment is unpublished.
Sam · 3 years ago
does it auto update when new data is added?
- Reply
- To post as a guest, your comment is unpublished.
  Yara · 1 years ago
  
  No, and I'm trying to find the answer
  
  Reply
- To post as a guest, your comment is unpublished.
  Designedforx · 3 years ago
  
  Does not seem like it
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Michael Riddering · 2 years ago
  
  Did you ever figure out how to get this to auto update?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Tomasz B · 2 years ago
  
  Did anyone find a way to auto update?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Sarah · 2 years ago
  
  I need to figure out how to get it to auto update too!... Anyone find the answer?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Eduardo Reyes · 2 years ago
  
  Did anyone find a way to auto update?
  
  Reply
To post as a guest, your comment is unpublished.
Terry · 3 years ago
I keep getting an error "Action not allowed (Line 0)". What does this mean?
- Reply
To post as a guest, your comment is unpublished.
Terry · 3 years ago
I'm getting "Action Not Allowed Line 0" What am I doing wrong?
- Reply
To post as a guest, your comment is unpublished.
KydKat · 3 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.
- Reply
To post as a guest, your comment is unpublished.
Laura · 3 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
- Reply
- To post as a guest, your comment is unpublished.
  Patodox · 2 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();
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Alberto · 1 years ago
  
  cambio la coma (,) por punto y coma (;) pero aun así no me cuenta el color. me sale como resultado (0 cero).
  
  Reply
- To post as a guest, your comment is unpublished.
  rtapia@colegiohuinganal.cl · 3 years ago
  
  Hello, I have the same problem, have you fix it?
  
  Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 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!
  
  Reply
To post as a guest, your comment is unpublished.
Dave Gorman · 3 years ago
Your sumColoredCells() function is incorrect as it doesn't use sumRange
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 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!
  
  Reply
To post as a guest, your comment is unpublished.
Todd · 3 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.
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 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!
  
  Reply
To post as a guest, your comment is unpublished.
Jose · 3 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
- Reply
To post as a guest, your comment is unpublished.
Sheldon · 3 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.
- Reply

あなたのアカウントにログイン

パスワードのリセット