How to count colored cells in Google Sheets

When you use different colors to organize your data in Google Sheets, you might find there are no built-in formulas to easily summarize data based on color. To address this, we introduced several custom functions that take into account both the font and background color of cells for basic operations, making it possible for your Google Sheets to sum by color or count cells by color.

You will find these functions among the smart 40+ features of our Power Tools collection or as an individual tool: Function by Color. Both offer a 30-day trial period and two subscription plans: 12-month and lifetime.

How to count colored cells in Google Sheets

Let's take one of the most common tasks with colored cells in a spreadsheet: counting cells that have the same formatting. Say, here I have a list of grades and I want to see how many times each test was passed, i.e. count all green cells in a column: Count colored cells with different types of values.

If you're dealing with ranges that include various data types like numbers, text, or dates, consider using the COUNTA function from the Function by Color tool.

For a start, open the tool to sum by color. In Power Tools, you will see it right on the smart toolbar: Open Function by color tool in Google Sheets.

Here are six simple settings you need to specify to get the results:

  1. The range with the color-coded data that you want to check: I pick the columns with the test results, B2:D18.
  2. The pattern cell. The function will take its font and/or background color and look for the same in other cells. I pick the fill color of B2 as an example.
  3. The function. To count cells with a particular number format, it's best to choose the COUNTA function because it is the only one that works with non-numeric values. This way you can be sure that the data format in the cells will not impede the results.
  4. The place for results. I want my Google Sheets count cells by color in each column so it is three cells for me, B19:D19.
  5. How to apply it. You can choose to count cells in each row, column, or in all selected cells. As I want to count green cells for each test, I choose to Calculate in each column. If I wanted a general result for all three tests, I'd choose the entire range.
  6. The formatting of cells with the result. You can have them filled with the same colors as you count by selecting just one checkbox.
Use Sum by color to count green cells.

Once I click Insert function, the tool adds the formula under each column, so I will see the results for each test: The function shows how many green cells are in each column.

Let me break down its syntax and explain each part:

  • =LAMBDA(vbc, IF(ISERROR(vbc), vbc is the service part that enables the formula to return exactly what Google Sheets replies with, whether it's "loading", some error, or the calculation result.

    Tip. You'll see this part only for COUNT, COUNTA, and COUNTBLANKS functions because of their peculiarities.

  • COUNTA(vbc) is one of standard Google Sheets functions you pick to use with the colored cells. vbc stands for valuesByColor.
  • valuesByColor is our custom function that handles colors.
  • light green 3 is the cell color considered for the calculations, i.e. the background color of the pattern cell.
  • "" is empty since I don't consider the font color of the pattern cell.
  • D2:D18 is the range to check for colored cells. In my example, it looks at cells in column B, C & D respectively.

How to sum colored cells in Google Sheets

Say, I'm keeping track of classroom equipment orders. I denote the ordered things by yellow background color, items on the way by blue, and delivered equipment by green: Sum same-colored cells.

My task is to see how many desks, computers, and other supplies are on the way at the moment. Color is the only difference these numbers have in my table. So I open the tool to sum by color and use the following settings:

  1. I select the entire range with my data to check it, B2:F8
  2. I pick C2 as a pattern cell to specify the format of items I want to calculate.
  3. Select the SUM function to tally numbers from blue cells.
  4. Pick cells to place the resulting calculations for each item, G2:G8
  5. To see the number of shipped items for every product, apply the function to each row.
Sum values by color.

Click Insert function to get the formula after each line in your table. See the results inserted for each line.

The convenience of getting the formula is that you can modify any of its parts and paste it wherever you need in your Google spreadsheets.

Google Sheets: sum by all colors in the table

The updated Function by Color lets you calculate not just one color per formula but all colors simultaneously.

Using the same data as above, I can find the total of all delivered supplies (green), tally those on their way (blue) or those ready for ordering (yellow).

I just need to switch to the All colors tab in the add-on and adjust the same settings:

  • The range is the same table, B2:F8.
  • I will sum data by all fill colors.
  • I also choose to calculate the entire range and have 1 formula inserted in A10.

      • Note. The formula returns the result in 2 columns: color & the total. So make sure there's enough space to insert the function. The result will show once there's room for it, but until then you may see the #REF! error if other data is on the way. Sum by all colors in Google Sheets.

Update the results

So, Google Sheets doesn't have native functions that work with color. This means your spreadsheet doesn't treat changes to cell formatting as a reason to re-calculate your formula results. The good news is that both workarounds in-store are elementary :)

  1. You can change any value within the calculated range. Say, you are counting green cells in A1:C254. You can simply add a character to any of the cells in this range, and then remove it to get the updated results.
  2. If you have a lot of valuesByColor formulas in your sheet, click on the Refresh results option (right under the Function by color in Power Tools or under the Extensions menu for the single add-on) to update all formulas in one go. Refresh Sum by color results.

Edit formulas

Another way to change the result is to edit the entire formula. But if there are only a couple of settings you'd like to choose, you don't have to build the entire formula anew. It is enough to click a cell that already contains a formula and pick the option to edit it: Edit selected valuesByColor formula.

The add-on will open with all the settings necessary to create this selected custom formula. You can adjust some or all of them — ranges, colors, pattern cell, function — and insert everything back.

Troubleshooting

If you see an error in place of the formula, you may be working with a file that has no locale. When this happens, our function doesn't know what delimiters it should use, so it ends up giving you an error. If you see this, please go to File > Settings in Google Sheets and make sure you have the locale set.

Video: How to count colored cells in Google Sheets


Table of contents

166 comments

  1. Hey, i want to count green cells but it's not working.
    Can you help me ?
    Thanks

    • Hey Denis,

      Thank you for sharing your file. I've just opened it and the result of the formula states 4, which is correct. By default, COUNTA counts all coloured cells in a dataset, including text values and empty cells.
      Please let me know the result that you expected, and I'll help you pick the correct function.

  2. This hack saved me! Another website was making use a script but it was useless. This was easy! Thank you!

  3. I have STRAWBERRIES on RED colour and APPLES on RED COULOUR. (strawberries on green and also apples on green)
    I used this "=COUNTA(valuesByColor("#ff0000", "#000000",'2-6 SEP'!5:17))" but I get the total of both fruits. I need to know how many apples on red colour I have.

    Where should I add and what, in my formula, in order to get the correct answer.
    Thank you in advance!

  4. Hi, I was wondering what function to use to count color in a range with, say, the letters "AB" in the cell also. So count red cells that have "KO" in them.

    • Hi Ben,

      To count cells based on several conditions, you would normally use COUNTIFS. However, since our valuesByColor returns the list of values ​​from cells, it cannot be wrapped in COUNTIFS.
      We've been asked to introduce this functionality and are going to check if it's technically possible to implement something like this in the future. But for now, I'm afraid there's no simple workaround. I can only offer to try and use Scripts. Here's an overview of Google Apps Script with a lot of helpful content and links:
      https://developers.google.com/apps-script/overview

      I wish I could help you better.

  5. Is it possible to quickly edit the formula once it has been set? For example, if the pattern cell needs to be changed, is there a quick way to accomplish this? Also, if the range shifts in sheets, will the tool auto adjust?

    • Thank you for your questions, Colin.

      Once the formula is set, you can edit its arguments in the formula bar.
      If the pattern cell changes, the quickest way to check its new hex colors is to select the cell, click Fill/Text color > Custom. You'll see what color is used in the pattern cell right away. You can copy and paste it to the formula replacing the existing color.

      Yes, currently, if the range shifts (within the same sheet), the formula auto adjusts.

  6. I would like to count cells which are marked green. The goal is to know how often people have been available for something which we code with green and red background of the cell.
    Is there a possibility to count the green background cells if there is no number/text in the cell?
    Thanks for your support.

    • At the moment, Function by color counts all cells with the same background, whether blank or with data.
      To count only empty cells, create a formula with our tool and then manually substitute COUNTA with COUNTBLANK in it.

  7. When I sort this column, it is changing the data of the row. The forumal in row 2 will say it is counting row5. How do I get the rows to lock while sorting?

  8. I'm trying to use the sort by color COUNTA function in order to quickly tally my schedule items (which I have sorted by colour). In my schedule, I have time increments of 15 minutes, but many things obviously take longer than that; in these cases, I merge the cell to take however much time it needs. In my tally sums, I generally just count up the number of 15 minute periods something takes and then multiply it by 15 for the total number of minutes. Unfortunately, though, I can't seem to find a way to make the sort by color function recognize these merged cells as covering more space than a single cell. Is there a good way to do this?

  9. Hi, excuse me I just want to filter by color, is this possible?

  10. First formula I try using the wizard, I got this:
    =====
    Error
    TypeError: Не удается обнаружить функцию indexOf в объекте TypeError: Не удается прочитать свойство "sBackgroundPatternArg" объекта null.. (line 3082).
    =====

    I tried aligning the функцию below the объекта threshold, but it didn't свойство...

    Большой!

  11. Hi Irina,

    I'm using the valueByColor function to count the number of cells in a range with certain background color, as exposed in this article:

    =(COUNTIF(valuesByColor("#ffff00","",B7:AF7),""))

    The above works as expected, detecting the cells in the range with yellow background. Nonetheless, when I do the same for the green background, it doesn't work at all:

    =(COUNTIF(valuesByColor("#00ff00","",B7:AF7),""))

    I can provide access to the google sheet so you can investigate this issue.

    Thank you.

    • Hector:
      There are many colors people call green and each variant has an individual hex code.
      Here are some codes I found on the web by searching "color codes". Try some of these codes in your formula and see if one of them works.
      lawngreen #7CFC00 rgb(124,252,0)
      chartreuse #7FFF00 rgb(127,255,0)
      limegreen #32CD32 rgb(50,205,50)
      lime #00FF00 rgb(0.255.0)
      forestgreen #228B22 rgb(34,139,34)
      green #008000 rgb(0,128,0)
      darkgreen #006400 rgb(0,100,0)
      greenyellow #ADFF2F rgb(173,255,47)
      yellowgreen #9ACD32 rgb(154,205,50)
      springgreen #00FF7F rgb(0,255,127)
      mediumspringgreen #00FA9A rgb(0,250,154)
      lightgreen #90EE90 rgb(144,238,144)
      palegreen #98FB98 rgb(152,251,152)
      darkseagreen #8FBC8F rgb(143,188,143)
      mediumseagreen #3CB371 rgb(60,179,113)
      lightseagreen #20B2AA rgb(32,178,170)
      seagreen #2E8B57 rgb(46,139,87)
      olive #808000 rgb(128,128,0)
      darkolivegreen #556B2F rgb(85,107,47)
      olivedrab #6B8E23 rgb(107,142,35)

    • Hi, Hector,

      If it's not a big trouble for you, yes, please share your spreadsheet with us (gapps.ablebits@gmail.com) with a description of your task.

      We'll take a look at the problem and do our best to help.

  12. La fórmula es: =SUM(valuesByColor("#00ff00"; "#000000"; Grupos!B4:B87))

  13. Hi, Irina
    I am troubled to figure out how to change a =sum formula based on the cell background color.
    I have applied a color (black) conditional formatting to a column based on a text value (Canceled). Then I have color-matched another column with the "onEdit" function of the script editor based on the results on the first column, but I want the =sum formula on the second column which cells background color is black to automatically change to a zero value.
    Is this possible to achieve? Thank you in advance for your help.

  14. I have tried using your sum by color formula as instructed but keep receiving an error I don't understand:
    "TypeError: Не удается прочитать свойство "sBackgroundPatternArg" объекта null. (line 3073)."

    • Hello Sandra,
      Please go to File - Spreadsheet settings and set a locale for your file. This should fix the issue. Please let me know if it doesn't.

  15. Hi! I have a sheet that I'm working with that I can't seem to get the Power Tool to refresh. I am trying to count a certain color in a range of cells. This is the formula I'm using:
    =COUNTA(valuesByColor("#ffff00", "#000000", '1st Nine Weeks'!E5:I80))
    I am only getting a response of 1 and when I go to refresh the arrows just keep twirling and nothing changes. Help please :) Thank you!

    • Hi Emily!
      Sorry to hear that. Please make sure you have a locale selected for this spreadsheet (go to File - Spreadsheet settings). If it is set, could you specify if you are the owner of the document? Please also check what errors you see on the Console tab if you press F12 on your keyboard (Cmd+Alt+j if you have a Mac). We'll do our best to assist you.

  16. I have cells that are color coded with dates in them. I want to get a count of cells that are a certain color and within the month of November. (eg (valuesByColor("#9fc5e8", "#000000", Sheet1!D3:D49), >=11/1/2017 and <=11/30/2017) I've been unable to get this to work. Can you provide the formula for this? Thanks.

    • Hi David,
      Our function can't look at more than one condition. You can do this only with the help of an array formula, here is the one you need to use:
      =sum(ARRAYFORMULA(--(11=arrayformula(month(valuesByColor("#9fc5e8", "#000000", Sheet1!D3:D49))))))
      We hope this helps!

      • That works, thanks!!

      • Irina, sometimes this formula returns a 1 when it should return a 0. How can I fix that?

  17. Hi there,

    I'm using =COUNTA(valuesByColor("#6d9eeb"; 'Top Slice Full Scope'!D2:D59)) but it only counts 1 cell. If I use =valuesByColor("#6d9eeb"; 'Top Slice Full Scope'!D2:D59) it shows an error: Error
    TypeError: Не удается прочитать свойство "sBackgroundPatternArg" объекта null. (line 3073).

    Can you help?

    • Hi Marta,

      Most likely the locale is not set in your spreadsheet and the function doesn't know what delimiter it should use. Please go to File - Spreadsheet settings and select some locale there, it should fix the issue.

  18. I wish to count the number of cells with a particular value AND color.
    How?

    Jon

    • Hello Jon,

      You can use the COUNTIF function for that, e.g.:
      =COUNTIF(valuesByColor("#ff0000", "#000000", 'Sheet name'!A1:B100),"value")

  19. Hi Irina - I have downloaded the add in as I want to sum by colour (I cam across your article on how to do this)

    ...However , the Sum by colour option is not showing in the formula dro pdown for sum

    Can you advise ?

    Thanks
    Jim

    • Ignore that... User error!

      I was looking in the auto sum drop down

  20. Is there a reason why the tool won't count something that is in comparison? For example, I am trying to keep track of 2 different records like "3-1"... my current formula I have is...
    =COUNTA(valuesByColor("#97f3c3","", G4:G11))&"-"&COUNTA(valuesByColor ("#ff9b9b","", G4:G11))

    I have updated the spreadsheet settings > locale.
    It is stuck at 1-1 for some reason.

    Thanks in advance!

    • Hello Oscar,

      I'm afraid one SumByColor formula can only calculate one color within one range. Please try to enter one formula for each color you want to count, and then subtract one result from another.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)