How to count cells by color 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. This is why we added a custom function that considers the font and background color of the cells when applying all basic operations.

You can find it among the smart features of our Power Tools add-on or as an individual tool: Function by Color. Both offer a 30-day trial period and two subscription plans: 12-month and lifetime.

Tip. Watch this short demo video or look through the detailed description with the screenshots below.

How to count colored cells

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 work with similar ranges that comprise different types of records: numbers, text, or date values, embrace the COUNTA function with 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.
  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. When you want to count cells with particular formatting, it's best to choose the COUNTA function because it is the only one that works with non-numeric values. This way we can be sure that the format of the data in the cells will not impede with the results.
  4. The place for results. I want to count in each column so it is three cells for me.
  5. How to apply it. We 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:

  • =COUNTA is one of standard Google Sheets functions you pick to use with the colored cells.
  • ligh 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.
  • B2:B18 is the range to check for colored cells. It looks at cells in column B in my example.

How to sum values by color

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.
  2. I pick C2 as a pattern cell to specify the format of items I want to calculate.
  3. Select the SUM function to add up the numbers from the green cells.
  4. Pick cells to place the resulting calculations for each item.
  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.

Update the results

Do you remember that Google Sheets don't have functions that work with color? This means they don't count changes to cell formatting as a reason to re-calculate your formula results. The good news is that both workarounds you can use are very simple.

  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 option that is right under the Function by color in Power Tools 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's 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 setting that were used to create the selected formula. You can adjust some or all of them – ranges, colors, pattern cell, function – and insert everything back.


If you see an error in place of the formula, you may happen to 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 > Spreadsheet settings... in Google Sheets and make sure you have the locale set.

See also

161 comments to "How to count cells by color in Google Sheets"

  1. Alicia says:

    Is there a way to combine this with an IF function or something. I have a time off request form for my employees to fill out online and I pull the data from that into sheets. I want to highlight approved time off and have it automatically calculate by employee. I can color code by employee and get the right numbers since right now we're small, but I really don't want to use that many colors. I shared a sample with you. Let me know if this is something that can be done.

  2. MaKayla McGee says:

    Doesn't work. I have tried every solution posed by reponses to other questions. The Locale is set to the United States. I have refreshed the add-on, the webpage, and the browser. I have tried every combination of COUNT function, hex code vs color names, column, row, and entire range. I get a 1 calculated every time.

    Not worth the time.

    • Hello MaKayla,

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

      Sometimes it's wrong colors, sometimes the wrong formula or even your data format that cause the issue (e.g. when you enter a dollar sign right into a cell and believe it's a currency but it's actually text for Google Sheets). If you'd like, you can share your spreadsheet with us: I'll look into it and suggest what may be causing the problem.

  3. ezzanur ameera says:

    hi, I already share my google sheets with you. Can you please take a look with the formula that i want to use which is count cells by color in Google Sheets.

    thank you

  4. Arun Kumar Singh says:

    How to count conditional formatting based on color by name and name is given by another column in google sheets

    • Hello Arun,

      For me to be able to help you, please share a small sample spreadsheet with us ( with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance and often gives us a better understanding than any text description.
      I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task.

  5. Neha Bansal says:

    I Have an issue when is select the cell with colour always results showing 1.
    but in my file multiple columns have single colour but still showing 1.

    Please help

    • Hello Neha,

      First, please go to File > Spreadsheet settings and see if you have a locale selected there. If not, please set one and confirm your action, then select the Refresh results in the tool, the function should work correctly then.
      Also, if you're not the owner of the spreadsheet, ask the owner to start the add-on on their side and click Refresh results for you.

      If these don't help, please share your spreadsheet with us:, and specify where the problem formulas are. I'll look into the issue.
      To grant us access to your data, press the Share button at the upper right corner of Google Sheets and enter
      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  6. Rhys says:

    My results always come back with 1.
    Even though I can hand count and see 18...

    My formula:
    =COUNTA(valuesByColor("#4285f4", "", A:A))
    Or I add "black", still same result.

    I've tried with multiple colours and always returns the same result as 1...

    Is it just me?

      • Hello Rhys,

        A lack of locale in the spreadsheet may prevent the function from calculating the results as it doesn't know what delimiter should be used. Please go to File > Spreadsheet settings and see if you have a locale selected there. If not, please set one and confirm your action, then Refresh results.

        If this doesn't help, please share an editable copy of your spreadsheet with us:, I'll look into the problem. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

        Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.

  7. Alex says:

    What if I wanted to count how many times, for example, the text "apple" occurred with the yellow cell colour? "apple" occurred in the purple colour? "orange" occurred with the yellow cell colour? Etc

  8. Adrian says:

    I am experiencing the same thing. The results are always zero. May I please ask for a sample spreadsheet? Thank you in advance.

    • Hello Adrian,

      I'm sorry, I'm afraid we don't have the spreadsheet with examples for this blog post saved.

      For us to be able to help you, please consider sharing a small sample spreadsheet with us ( with your data AND the formula that returns zeros. I kindly ask you to shorten the table to 10-20 rows.
      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.

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

  9. Alexandra Colour says:

    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!

  10. Ben says:

    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.

  11. Colin J Gallagher says:

    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.

  12. Timo says:

    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.

  13. MIKE says:

    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?

  14. Katie says:

    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?

  15. Daniel Beaudry says:

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

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


  16. Héctor says:

    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:


    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:


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

    Thank you.

    • Doug says:

      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)

  17. Giovanna says:

    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.

  18. Sandra says:

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

  19. Emily says:

    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.

  20. David says:

    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.

  21. Marta says:

    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?

  22. Jim says:

    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 ?


  23. Oscar says:

    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!

  24. Станислав says:

    Ирина, большое Вам спасибо!
    Очень долго боролся с проблемой по посчету ячеек по цвету.

  25. Chris says:

    I am trying to count colored cells based on criteria in another column.
    For instance, if column E2:E322 contains the text, "Wind ensemble," count the colored cell in the adjacent row F. I have a total count of colored cells in the row "F", but would like to split that by another criteria in another row.

    I've also tried filtering the data into another spreadsheet, but the formatting doesn't copy using the filter tool.

    • Hello Chris,
      I'm afraid Sum by Color doesn't support two conditions. You can try one workaround:
      - Create one helper column for each color you want to check next to your data. Use Sum By Color with the "COUNTA" function and calculate "in each row". This way you will get "1" whenever the cell in column F has the color you are looking for. I.e. you should get the following type of formula in G2:
      =COUNTA(valuesByColor("#9fc5e8", "#000000", 'Main sheet'!F2))

      - Enter SUMIF function in another cell to count all cells with this color using the values in column G, while checking the necessary text value in column E as well:
      =SUMIF(E2:E322,"Wind ensemble",G2:G322)

      I hope this helps.

  26. Becky says:

    I am having trouble getting the Sum by Color to work. I have looked through all the comments in this thread, and not certain what I am doing wrong. I installed Power Tools, did the Sum By Color, selected the Pattern Cell Color, Source Range is from J15:J169, COUNTA, each column, results to E180. No matter which color I choose, the amount calculated is always 1.

  27. Zane says:


    I want to only count the cells that have a fill of white (#ffffff) that have text in it?

    Currently when i use the below formula it is counting the blank cells as well the cells that have text in it.
    =COUNTA(valuesByColor("##ffffff", "#000000", 'Drawing Register'!A4:K706))

    Can you provide some help on this?

    • Hi Zane,

      As the COUNTA function includes blanks, you need your formula to calculate the difference between all cells formatted this way and empty cells:

      =COUNTA(valuesByColor("#ffffff", "#000000", 'Drawing Register'!A4:K706))-COUNTBLANK(valuesByColor("#ffffff", "#000000", 'Drawing Register'!A4:K706))

      Please let me know if you have any questions.

      • Rob says:

        Hi Irina,

        thanks a lot for the add-on and all this useful comments. For this particular case the "COUNTA - COUNTBLANK" didn't work very well in the same function, returning 1 upon refresh. To whom may read for me the workaround was: "COUNTA" in a cell "COUNTBLANK" in another and then the simple difference "ex:a14-b14" in a third cell.

        cheers :3

  28. Grnldy says:

    Good morning!

    Thanks so much for the awesome add-on. When I try the Sum Color function, I keep getting an error that says "circular dependency detected". To my knowledge I do not have any other formulas in the sheet, so I am a little confused. Any help would be appreciated! Thanks in advance!

  29. Felipe Gomes says:

    Hi, Irina!

    First of all, thank you guys for this formula and for Power Tools! It's been working wonders around here!

    However, I would like to combine this formula with a VLOOKUP. For example: I have a list of names and different values along multiple rows in another sheet and I have changed the BG color for some of these values. Something like:

    Felipe 1 2 3
    Irina 4 5 6
    Felipe 7 8 9

    I would like to know how many colored cells one name has. I tried using Sum By Color in an IF formula with VLOOKUP as its condition, but it didn't work.

    =IF("Lance"=VLOOKUP("Lance";WEST!$B$2:$B$450;1;0);SUM(valuesByColor("#ff0000"; "#000000"; WEST!$C$2:$U$450)))

    Is there a way I can make it work? If not, thanks anyway. The Sum by Color's been very helpful. 8)

    • Hi Felipe,

      We really appreciate your kind words!

      You're right, there is no straightforward way to count values by color and another condition as our tool doesn't support functions that work with more than one range. There is one workaround you can use if your sample values are in the same sheet:
      - Add a helper column where you'll sum values by the necessary color in every row. E.g. if your numbers are in columns B:D, you will have this simple formula in column E:

      =SUM(valuesByColor("#d9ead3", "#000000", Sheet18!B1:D1))

      - Then sum values in column E by the necessary name in column A, e.g.:

      Here is a link to your simple example:

      If your task is different, please share a sample spreadsheet, we'll do our best to help.

  30. Rony says:

    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.

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

      • Rony says:

        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.



  31. Morgan Williams says:

    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?

    • 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, we'll look into it.

  32. KB3 says:

    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

      • Stephanie G says:

        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.

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

  33. Marielle says:

    Hi there,

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



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

  34. james says:

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

    Circular dependency detected.

    Do you know what I might be doing wrong?


  35. Esther says:

    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?


  36. Lea says:

    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.

  37. Ling says:

    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.

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

  38. Brobinson says:

    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?

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

      • Brobinson says:

        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.


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

  39. Jurate says:


    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?

  40. Michael Oliver says:

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


    • 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 We'll do our best to assist you.

  41. Lucy says:

    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?

  42. troels mouritzen says:


    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.

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

  43. dfc says:

    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.

  44. Meghan says:


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

    • Meghan says:

      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.

  45. Evelyn says:

    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.

  46. Mt says:

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

  47. joel says:

    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?

  48. Rick says:


    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?


    • 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

  49. Greg says:

    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.

  50. Andri says:

    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

  51. Justin says:

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


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

  52. Em says:

    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?

  53. GG says:

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

  54. André Martins says:

    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.

  55. Nulrek says:


    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.

  56. Cristóvão Resende says:

    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?


  57. Eugene says:

    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.

  58. Mishelle says:

    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

      We'll look into it.

  59. Brandon says:

    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.

  60. Adam says:

    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.

      • Quang Toan says:

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