Google 시트에서 셀 색상을 기준으로 셀을 계산하거나 합계하는 방법은 무엇입니까?

다음 스크린 샷과 같은 결과를 얻기 위해 특정 셀 배경색을 기반으로 셀 값을 계산하거나 합산합니다. 이 기사에서는 Google 시트와 Microsoft Excel 에서이 작업을 해결하는 방법에 대해 설명합니다.

Google 시트의 스크립트로 셀 색상을 기준으로 셀 값 계산

Google 시트의 스크립트를 사용하여 셀 색상을 기준으로 셀 값 합계

Microsoft Excel에서 Excel 용 Kutools를 사용하여 셀 색상의 셀 값 계산 또는 합계

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에서 Excel 용 Kutools를 사용하여 셀 색상의 셀 값 계산 또는 합계

Microsoft Excel에서 특정 셀 색상을 기준으로 셀 값을 계산하거나 합계하려면 Excel 용 Kutools의 색상으로 계산 유틸리티를 사용하면이 작업을 최대한 빨리 완료 할 수 있습니다.

Excel 용 Kutools : 300 개 이상의 편리한 Excel 추가 기능으로 30 일 동안 제한없이 무료로 사용해 볼 수 있습니다..

다운로드로 이동
30 일 무료 평가판 매수
PayPal / MyCommerce

설치 후 Excel 용 Kutools, 다음과 같이하십시오.

1. 셀 색을 기준으로 계산하거나 합할 셀 범위를 선택한 다음 Kutools Plus > 색상으로 계산, 스크린 샷 참조 :

2. 에서 색상으로 계산 대화 상자에서 표준 서식 인사말 색상 방법 드롭 다운 목록을 선택한 다음 배경 인사말 개수 유형 드롭 다운, 스크린 샷 참조 :

3. 그런 다음 보고서 생성 버튼을 클릭하면 계산 된 결과가 포함 된 새 워크 시트가 한 번에 생성됩니다. 스크린 샷 참조 :

주의 사항:이 강력한 기능을 사용하면 조건부 서식 또는 글꼴 색상을 기반으로 셀 값을 계산할 수도 있습니다.

지금 Excel 용 Kutools 다운로드 및 무료 평가판을 클릭하십시오!

최고의 사무 생산성 도구

Excel 용 Kutools는 대부분의 문제를 해결하고 생산성을 80 % 향상시킵니다.

재사용: 빠르게 삽입 복잡한 공식, 차트 그리고 이전에 사용한 모든 것; 셀 암호화 암호로; 메일 링리스트 생성 이메일 보내기 ...
슈퍼 포뮬러 바 (여러 줄의 텍스트와 수식을 쉽게 편집 할 수 있습니다.) 레이아웃 읽기 (많은 수의 셀을 쉽게 읽고 편집합니다.) 필터링 된 범위에 붙여 넣기...
셀 / 행 / 열 병합 데이터 손실없이; 셀 내용 분할; 중복 행 / 열 결합... 중복 셀 방지; 범위 비교...
중복 또는 고유 선택 행; 빈 행 선택 (모든 셀이 비어 있음); 슈퍼 찾기 및 퍼지 찾기 많은 통합 문서에서; 무작위 선택 ...
정확한 사본 수식 참조를 변경하지 않고 여러 셀; 참조 자동 생성 여러 시트에; 글 머리 기호 삽입, 확인란 등 ...
텍스트 추출, 텍스트 추가, 위치 별 제거, 공간 제거; 페이징 부분합을 만들고 인쇄합니다. 셀 내용과 주석 간 변환...
슈퍼 필터 (다른 시트에 필터 구성표 저장 및 적용) 고급 정렬 월 / 주 / 일, 빈도 등 특수 필터 굵은 기울임 꼴로 ...
통합 문서와 워크 시트 결합; 키 열을 기반으로 테이블 병합; 데이터를 여러 시트로 분할; xls, xlsx 및 PDF 일괄 변환...
300 개 이상의 강력한 기능. Office / Excel 2007-2019 및 365를 지원합니다. 모든 언어를 지원합니다. 기업 또는 조직에 쉽게 배포 할 수 있습니다. 전체 기능 30 일 무료 평가판. 60 일 환불 보장.

자세히보기 ... 무료 다운로드... 매수...

Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만듭니다.

Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, Publisher, Access, Visio 및 Project.
새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 만듭니다.
생산성을 50 % 향상시키고 매일 수백 번의 마우스 클릭을 줄입니다!

자세히보기 ... 무료 다운로드... 매수...

54 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.
chan · 1 months ago
thanks, :)
- Reply
To post as a guest, your comment is unpublished.
Hawjin · 3 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 · 3 months ago
it errors for me :/
- Reply
To post as a guest, your comment is unpublished.
Milan · 3 months ago
@Andrew I have the same proble. Am I missing anything?
- Reply
To post as a guest, your comment is unpublished.
Davide · 8 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 · 10 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.
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.
sudarshan · 1 years ago
@coolkev99 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.
skyyang · 1 years ago
@Lenore 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.
Lenore · 1 years ago
@Brian Do we paste all of this to replace the original code?
- 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.
Alberto · 1 years ago
@Patodox 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.
Yara · 1 years ago
@Sam No, and I'm trying to find the answer
- Reply
To post as a guest, your comment is unpublished.
Yara · 1 years ago
@coolkev99 I have the same issue and don't know how to fix it
- 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 · 1 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.
Francisco · 1 years ago
Thank you so much, truly useful!
- Reply
To post as a guest, your comment is unpublished.
Roberto · 1 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.
Eduardo Reyes · 2 years ago
@Sarah Did anyone find a way to auto update?
- Reply
To post as a guest, your comment is unpublished.
Sarah · 2 years ago
@Tomasz B 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.
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
@skyyang 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.
skyyang · 2 years ago
@Geoff 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
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.
jchew · 2 years ago
Thanks, this worked great!
- Reply
To post as a guest, your comment is unpublished.
Tomasz B · 2 years ago
@Michael Riddering Did anyone find a way to auto update?
- 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.
Patodox · 2 years ago
@Laura 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.
Michael Riddering · 2 years ago
@Designedforx Did you ever figure out how to get this to auto update?
- 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.
Designedforx · 2 years ago
@Sam Does not seem like it
- Reply
To post as a guest, your comment is unpublished.
rtapia@colegiohuinganal.cl · 3 years ago
@Laura Hello, I have the same problem, have you fix it?
- 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.
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.
skyyang · 3 years ago
@Laura 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.
skyyang · 3 years ago
@Dave Gorman 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.
skyyang · 3 years ago
@Todd 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.
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

귀하의 계정에 로그인

비밀번호 재설정