Comments on: How to count colored cells in Google Sheets

If you color-code your data in Google Sheets, you've probably noticed the lack of standard formulas that summarize this kind of information. In this article you will learn how to use our add-on to count and sum cells by color. Continue reading →

Table of contents

Comments page 3. Total comments: 166

  1. Hi, I find this tool very useful, but unfortunately, I could not get it to work. I tried updating my spreadsheet setting, but I end up getting a 1 as a result (I manually counted and it should be 285) here is the formula that I get from Google Sheet:

    =COUNTA(valuesByColor("#f4c7c3", "#000000", 'Master File'!AF1:AF2110))

    The source range though is in another sheet within the workbook. Your help will be highly appreciated.

    1. Hi Rony,
      Thank you very much for you feedback. Please check if you have the locale set in Spreadsheet settings under the File menu. If you do, please try to click on the Refresh option in Power Tools, and specify if you are the owner of the sheet and if it is shared with many people? We'll do our best to assist you.

      1. Hi Irina,

        Thanks for the speedy reply. Unfortunately, the "Refresh" option looks like it is cross out. I will share the work sheet with you via the gmail account. Though to answer some of your questions:
        1. I am the owner of the Google sheet
        2. It is shared with 3 other people.

        I will request the other 3 to update their setting also, as that might be the issue.

        Again thanks for the speedy response.

        Regards,

        Rony

  2. I'm either getting lostin directions or what I want isn't possible. I'm trying to calculate how many time a red colored cell appears in a row. Is there any way you can simplify this?

    1. Hi Morgan,

      Here are the steps you can follow to do this:
      - Start Power Tools, click on the little arrow next to AutoSum tool at the top and select the Sum by Color tool to open it
      - Pick any red cell as your pattern cell
      - Select the range with all your data for the "Source range"
      - Select the "COUNTA" function
      - Choose the option to calculate in each row
      - Select the top cell for the calculation results in the last field
      - Click "Insert function"

      If you still have any issues, please share a sample spreadsheet with gapps.ablebits@gmail.com, we'll look into it.

  3. IP, I have the need to count the # of rows, in a 3 column span, but wasn't able to figure out how to get the range of columns figured out from the examples. I Used the following function and it appears to be working great on a single column (K).

    =COUNTA(valuesByColor("#f4cccc", "#000000", 'Review Compare'!K5:K567))

    Any thoughts on how I would count the total # of rows that have a specific bgcolor for columns I, J & K?

    Thanks, KB3

    1. Hello KB3,

      I'm sorry, but our function can only count the number of cells by color, it can't calculate the number of rows.

  4. Hi

    I keep receiving the error "Range Not Found", not sure what is going on here? Any advice?

    1. Hi Stephanie,

      Thank you for contacting us. Could you specify if you select the range in the same spreadsheet when you insert the function?

      Please send a screenshot of Sum By Color settings you choose and of the formula you get as a result to gapps.ablebits@gmail.com.

      We'll look into this issue.

      1. Hi Irina,

        Thank you I will send you a screen shot. The range I am using is the range of 1 column with multiple cell colors//values. I would like to get the sum of each cell color.

        1. Hi Stephanie,

          Thank you for the screenshot.

          The locale was not set for the file and the function couldn't calculate the results without knowing the delimiter it should use. If this happens again, please go to File -> Spreadsheet Settings and make sure Locale is selected, then start Power Tools and refresh the results.

          Feel free to contact me again if you have any other questions.

  5. Hi there,

    Is it possible for the formula to count data from multiple sheets? ie: Q1!A6:CM6, Q2!A6:CM6, etc?

    Thanks!

    Marielle

    1. Hi Marielle,

      Though it is not possible in one formula, you can create a master formula for the results of Sum By Color from each sheet, e.g. if you have the results in cell A7 in Q1 and Q2, you can enter the following formula into A8 in Q1:
      =A7+Q2!A7

  6. hi -- thanks for this article – it's exactly what i'm looking for. I keep getting this error in the cell:

    Error
    Circular dependency detected.

    Do you know what I might be doing wrong?

    thanks,
    james

    1. Hi James,

      You must have inserted the formula into the range where you are trying to count cells by color. Please enter it in a cell outside of the calculated range, this should fix the issue.

      Please let me know if you have any other questions.

  7. Hi Irina,

    When I do the =sum(ValuesByColor), all my inputs are fine and I'm able to follow all the steps. However my results end with an #ERROR! – either "Formula parse error" or "TypeError: Не удается прочитать свойство "sBackgroundPatternArg" объекта null. (line 3047)."

    What am I doing wrong, and how can I fix this?

    Thanks

    1. Hi Esther,

      Please go to File - Spreadsheet settings and select a locale there, this should fix the error.

      Please let me know if it doesn't.

  8. Hi Irina,

    What I want is to give me an exact number of cells shaded in red - these cells does not have value/data in it, just purely shaded. How to go about his please.

    1. Hi Lea,

      As long as the range you are checking is not completely empty, i.e. at east one cell has some data in it, you can select COUNTA function to get a number of all cells with a particular color.

  9. I just realized that I could not get results unless I added all colored cells before adding formula. Actually, I would like to add the formula first, then as people add colors in certain cells calculation occurs. Do you have any devices for those needs? Thank you.

    1. Hello Ling,
      You can still use Sum By Color, but you will need to click "Refresh" to see the results. Functions in Google Sheets do not update formula calculations when the background color changes. We can't affect this behaviour, which is why we introduced the "Refresh" option, you can find it right under Sum By Color. You can also change just one of the values in the calculated range instead, e.g. add and remove a character, the results should be updated then.

  10. It appears that when you chain calls to valuesByColor within the same formula the result is computed incorrectly. For instance:

    =COUNTA(valuesByColor("#f4cccc", "#263238", '2016'!J33:X37))

    Returns :: 17

    =COUNTA(valuesByColor("#f4cccc", "#263238", '2016'!J33:X37))+COUNTA(valuesByColor("#f4cccc", "#263238", '2016'!J33:X37))

    Returns :: 68 (oddly 17+17+17+17)

    Or as another example

    =COUNTA(valuesByColor("#f4cccc", "#263238", '2016'!J33:X37))

    Returns :: 17

    =COUNTA(valuesByColor("#f4cccc", "#263238", '2016'!J33:X37))+COUNTA(valuesByColor("#f4cccc", "#263238", '2016'!B42))

    Returns :: 36 (which we will note is 17+1+17+1)
    Of course a valuesByColor of a single cell can return only a 0 or 1

    Is there something about the operation of the function that I am missing?

    1. Hello,
      You are right, the function can't process more than one color in one formula, you need to enter a formula for each color you want to count. To combine the results, add a master formula that will sum them up, i.e. if you have the results by color in cells J38 and J39, then enter =SUM(J38, J39) in a different cell.

      We will consider adding the possibility to process more than one color in one of the future versions of the add-on.

      Please let me know if you have any other questions.

      1. In all cases provided the functions are only processing one color over multiple calls. I gather though that you response means that multiple calls to the valuesByColor function within the same formula contaminate the result due to compounding each call into the following.

        Thanks.

        1. Sorry for misinforming you. The issue is not only with the color, but with different ranges within one formula. When Google Sheets launches a custom function, we don't have the technical possibility to know which of the functions from the formula it processes when there are 2 or more of them. We always read the first range, which is why using the same ValuesByColor function in one formula is not possible. Our developers are looking for ways around this issue.

  11. Hello,

    We have been enjoying this function for a while with no problems. It is used on Google Form responses sheet where conditioning formatting is added. For some reason all results are showing 1. Tried refreshing but loading seems to happen in the other columns. It works just fine on brand new sheet.
    What might be the issue?

    1. Hello Jurate,

      Thank you for your feedback. Please check if the locale is selected in File -> Spreadsheet settings. If it is, could you share the spreadsheet with gapps.ablebits@gmail.com? We'll look for the reason why the function may not work there.

  12. Hey Irina,

    Thanks for this detailed & thorough breakdown - it will work for me eventually I'm sure but at the moment it's not, and I'm sure you can help me crack it!

    I've got a column filled with either Yes or No, with each cell possessing the conditional formatting of 'If text contains 'Yes' the cell turns green' and 'If text contains 'No' the cell turns Red'.

    I've tried following the steps, trying to add up the total of Yes-Green cells I have in the column, and it keeps returning a value of '1' when there's clearly more than 1 cell labelled Yes.

    Any ideas or help you can offer?

    Thanks a bunch!!!

    Michael

    1. Hi Michael,

      It looks like some error occurs when trying to calculate the results.
      First please make sure you use the COUNTA function as it is the only one that works with text values. Please also check if you have a locale set in "File-Spreadsheet settings".

      If this doesn't help, would it be possible for you to share a sample spreadsheet with gapps.ablebits@gmail.com? We'll do our best to assist you.

  13. Hi -
    Great add-on! As always, with solutions come further questions!:

    Is there a way to use this formula [valuesByColour] as a criteria within another formula?

    i.e. Of those cells formatted in RED, how many contain "XYZ" ?

    I am trying to build a formula using COUNTIFS, in this way but am having no luck. Is there a better way to do this, or can this not be used as a criteria?

    1. Hello Lucy,

      Thank you very much for your feedback!

      You can use our custom function as a criterion within another formula, e.g.
      =COUNTIF(valuesByColor("#ff0000", "#000000", Sheet1!A1:E12),"xyz")

      If this doesn't help, could you please describe your task in more detail?

      1. Hi Irina,
        That's perfect. Thank you!

        I think I was trying to add too many components!

  14. Hello,

    The powertools are working great for me. My problem is I would like to have it Count two colors.

    Like =COUNTA(valuesByColor("#00ff00" ; "#ff0000"; ""; Ark1!D2:D11))
    Which should count green and red background colors? or I would like it to do that.

    1. Hello Troels,

      We haven't provided for a way to process more than one color at a time, so you need to enter one formula for each color you want to count. The only way to combine the results now is to have a master formula that will sum up the results by each color, i.e. if you have results by color in cells D12 and E12, then enter =SUM(D12, E12) in a different cell to get the result you need.

      We will consider embedding this possibility in one of the future versions of the add-on.

      Please let me know if you have any other questions.

  15. P.S. In a row with no colored cells, it also puts a 1 when it fails.

    1. 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.

  16. 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.

  17. 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))

    1. 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.

      1. 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.

  18. 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.

    1. 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.

  19. 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))

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

  20. 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?

    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.

  21. 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

    1. 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

    1. Hello Sarah,
      Thank you for sharing your sample spreadsheet. Could you please make sure the locale is selected for your spreadsheet under File – Spreadsheet settings?
      If it is, please let me know if you inserted the function using the add-on, or entered it manually? We can’t seem to reproduce this issue, here is an example of the spreadsheet I created with the same data, you can see that the function calculates the results correctly:
      https://docs.google.com/spreadsheets/d/18v73MeMKfjBYYHbxwtRVAorDQLYehObfNMOMeBWE-bU/edit?usp=sharing

  23. 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.

  24. 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 ?

    1. 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

  25. 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

    1. 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.

  26. 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?

    1. Hello Em,
      I'm sorry to hear that you are having difficulties of this kind. It sounds like this is related to an issue with custom functions in Google Sheets that hasn't been resolved yet:
      https://code.google.com/p/google-apps-script-issues/issues/detail?id=4156

      However, please share a sample spreadsheet where it is reproduced with gapps.ablebits@gmail.com if possible, our developer will look into possible reasons why this may be happening.

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

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

  28. 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 ?

    1. 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.

  29. Hi

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

    1. 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.

  30. 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!

    1. Nevermind, solved it using counta.

      Thanks!

        1. having the same problem. any suggestions on how to resolve this?

  31. 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!

    1. 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.

  32. 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?

    1. 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.

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

    1. 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.

  34. 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?

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

    2. 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.

      1. 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.

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