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. P.S. In a row with no colored cells, it also puts a 1 when it fails.

    • Hello,
      I'm sorry to hear that you are having difficulties with the add-on. Would it be possible for you to share a sample spreadsheet where this issue occurs with gapps.ablebits@gmail.com? If you have any sensitive data, you can replace it with irrelevant information, just keep the format. We'll do our best to find what is causing this issue.

  2. I'm having the same problem mentioned above where when I run a refresh, it stays for some seconds and then goes back to 1. The weird thing is that I have about 80 rows and am using the count color function in two columns (with counta). When it fails, it fails in both columns, but it was working fine before I did some sorting. Now it's back to its pre-sort form, but about 7 scattered rows are having this problem. I did check the locale, and it says U.S. I've tried replacing them from Power Tools as well as by copying the cells above that are working. No go. Quite frustrating.

  3. Hello,

    I am using Sum by Color, but the number doesn't seem to update when I add or remove a color from the range it is counting.

    I had 9 "moderate green" cells (#92d050) which it counted perfectly. When I went back later to add another moderate green cell in the range, it did not update to 10. The font color was the same on the newly added cell, also.

    Any hints? Here is the formula being used:

    =COUNTA(valuesByColor("#92d050", "#000000", Sheet1!C116:C150))

    • Silly me, didn't try the refresh on Power Tools.

      But I do have a follow-up question. This is a shared doc with my colleagues. If they don't have pro, is there a way for them to refresh the values? It seems simply closing and reopening the Sheet does not resolve that problem.

      • Hello Meghan,

        You see, functions in Google Sheets do not "see" background color modifications, they update the results only when the values change. There is nothing we can do to resolve this, which is why we added the option that refreshes SumByColor results.

        If your colleagues do not have Power Tools, they can change just one of the values in the calculated range to trigger an update, for example add and remove a character.

  4. The tool does not seem to like negative numbers. Trying to add up numbers exported from a bank statement and it returns zero when it encounters a negative number.

    • Hello Evelyn,
      I'm sorry, but we couldn't reproduce this issue. Could you please specify what function you select for the formula? If possible, please share a sample sheet where this occurs with gapps.ablebits@gmail.com, we'll look into it. If you have any sensitive information, you can replace it with dummy records, just keep the format.

  5. I am having zero success. I am trying to add a column of values that are color coded. I only want to add specific colors into different fields. The Sum by Color option on the Power tools and the instructions are not working. This is the formula it gives me =SUM(valuesByColor("#d99594", "#000000", '4 Week Cycle'!N3:N6))

    • Could you please specify what you see as the calculation results? Please also make sure you have a locale set in File - Spreadsheet settings.

  6. I want to to use cells like a gantt chart with 1 cell = 30min.

    Is there a way assign a value to a cell in such a way or can it only count a coloured cell as a 1?

    • Hello Joel,

      If you want to take each colored cell as 30 minutes and get the result in hours : minutes, just divide the formula result by 48 (e.g =COUNTA(valuesByColor("#b6d7a8","", B2:B40))/48 ) and apply the Time format to the cell.

  7. Hi,

    When using sum by color and counting the number of cells that are a particular color. If there are no cells of that color, I don't want it to return a "0". Just a blank cell. Is there a way to do that?

    Rick

    • Hi Rick,
      There are two ways you can do this:
      1) You can use an IF statement, e.g.:
      =IF(COUNTA(valuesByColor("#f4f4f4", "#000000", B8:F11)),COUNTA(valuesByColor("#f4f4f4", "#000000", B8:F11)),"")
      2) You can use Conditional formatting in Google Sheets and choose white font color for zero values to hide them

  9. Wow, you are very responsive to your users! I wanted to count by color but, unfortunately, my company does not allow add-ins for google sheets.

  10. Hi, this is working great. How would I filter the sheet that it will only count the colours of the cells if it matches a date greater than something I will specify ?

    Any tips ?

    • Hi Andri,

      There are two ways you can do this. As our function can check only the color of the cells, you can combine it with the COUNTIF function in Google Sheets, e.g.:
      =COUNTIF(valuesByColor("#b7e1cd", "#000000", Sheet11!I2:I38),">="&I3)
      Here I3 is the date to compare against.

      You can also use conditional formatting first:
      - Go to Format -> Conditional formatting, choose to format cells if date is after exact date, enter your criterion and click Done;
      - Run Sum by color to count the formatted cells

  11. Hello there.
    Been trying multiple add ons on Google sheets. to count the sum of values in a particular colour.
    Finally, I am really close.. Thanks to your add on.
    My only other need is,
    The Power Tools gives me only 3 options
    "Insert function after: Entire range, Each Column, Each Row"
    Would it possible if I can insert / call the function in a cell on another page of the same excel sheet?
    Appreciate your help in advance :)

    Justin

    • Hello Justin,
      Thank you for your question.
      We are actually about to publish a new version of the add-on that will let you choose where to paste the results. You will still be able to add calculations for the entire range, each row, or each column.
      Now you can copy the formula to any place in the spreadsheet. However, if you want it to look at a different sheet, you need to include its name into the range like you do in regular formulas, e.g.:
      =COUNTA(valuesByColor("#d9ead3", "#000000", 'Sheet1'!D1:H11))
      Please let me know if you have any other questions.

  12. This is great, thank you. I did it and it was working perfectly fine yesterday. However, when I came back today to the sheet I had set it up on, the values are stuck on "Loading" and when I click on the cells it is telling me that 'valuesbycolor' is an unknown function. Opening up power tools and hitting refresh is not helping. Do I really have to go through the process of setting up the valuesbycolor every single time I use it?

  13. Mine only counts purple cells with text. I need ALL the purple cells... (campsites occupied, on my data sheet)

    • The only function that works with blank values is COUNTA, please use it in your ValuesByColor formula to get a count of purple cells.

  14. Hi there,

    I want to count my green cells and when i make the function with power tools it works.
    But when i close the document and open again. If i add more green cells the counter does not change.

    How can i fix it ?
    Can i work with it in the mobile too ?

    • Hi André,

      You see, functions in Google Sheets do not consider the change of background color as a reason to update the results. We can't affect this behaviour, so we introduced the "Refresh" option for our Sum By Color function. You can see it right under Sum By Color in the toolbar of the Power Tools add-on. As an alternative, you can change just one of the values in the calculated range, e.g. add and remove a character, to trigger an update.

      Add-ons are not supported on the mobile platform.

      I hope that you'll find this information helpful.

  15. Hi

    Does it still working as what I get currently if documents is share and view by collaborator who does not install power tools?

    • Hi Nulrek,

      If you insert the function using Power Tools and then share this spreadsheet with other users, the function will work. When you go to Add-ons -> Manage add-ons, please make sure Power Tools has the "Use in this document" option selected.

      There is one thing to keep in mind though: as custom formulas are not refreshed automatically, other users can update the results by changing any value in the range used in the formula, e.g. enter and remove a character.

  16. Hi there!

    When I use the formula =valuesByColor("#ff0000"; "#000000"; C9:L47) it returns the values that I'm looking for but when I use =count(valuesByColor("#ff0000"; "#000000"; C9:L47)) it just returns 0.

    Any thoughts?

    Thanks!

    • Nevermind, solved it using counta.

      Thanks!

  17. Hi Irina

    I have installed the Power Tools Add-on and would to use the Sum by Color funtion. For some reason it doesn't show me the Insert Function button as in your example. Am I missing something here?

    Many thanks!

    • Hi Eugene,

      Thank you for contacting us.

      Could you please make sure that your browser page is not zoomed in? Please try to press Ctrl+0 (or cmd+0 if you have a Mac) or make sure you have 100% in the browser settings -> Zoom.

      If this doesn't help, please go to Control Panel\Appearance and Personalization\Display and check what sizing option you have set there.

      Please let me know about the results.

  18. Hi,
    i have a table where cells are both empty and filled.
    I want to count the blank cells in a specific background color.
    I tried with Powertool Sum by color + countblank .. but it returns zero where i know it should return quite a high number instead.
    Could you help?

    • Hi Mishelle,

      I'm sorry, but we couldn't reproduce this issue. Please try to remove the standard function from the formula and see if you get any errors. E.g. if your formula looks the following way:
      =COUNTBLANK(valuesByColor("#b6d7a8", "#000000", C1:D9))
      Try to make it look like this:
      =valuesByColor("#b6d7a8", "#000000", C1:D9)

      If possible, please also share a sample spreadsheet where this issue occurs with gapps.ablebits@gmail.com

      We'll look into it.

  19. When I click refresh, the spinner goes on forever... My sheet has 15 counta by color cells only. What gives?

    • Hello Brandon,

      Thank you for contacting us.

      When you see this again, could you please press F12 on your keyboard (Cmd+alt+j if you have a Mac), go to the Console tab, and check what errors you see highlighted in red? Please send the text of the errors to us, our developer will look into this issue.

  20. Hey Guys,

    For some reason the code is working perfectly but after a short period of time it is showing 0 result for all count formula.
    Reseting the sheet will display the correct counts again but only for a brief period, maybe 20 seconds.
    Do you have any idea what might be causing this?

    • Hello Adam,

      I'm sorry to hear that you are having difficulties with the add-on.

      Could you please try to insert the same function from Power Tools and check if it behaves the same way?

      If it does, try to remove the standard function from the formula, e.g. =valuesByColor(A1,A1,A1:B10)

      Please let me know what error you see in the cell.

      • Hello Irina,

        I am having same issue as Adam mentioned above although I did exactly step by step you guided in this page.

        I tried many times and have same the result - it gives zero value for count/sum formula so I suppose this add-on is not perfect one to help us in counting/summing values by colored cell.

        Thank to check it on your side and any advice from you to solve our issue are highly appreciated.

    • That is because the app is limited for one use per day unless you give them money.

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 :)