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

164 comments

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

    • И вам большое спасибо за отзыв, Станислав! Приятно слышать, что наше решение приносит пользу.

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

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

  4. Hi,

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

      • 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

        • Thank you for your comment, Rob,
          You are absolutely right, the current version of Sum By Color can't process more than one function at a time, sorry for misleading you. Good to know you found the workaround!

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

    • Hello,
      Thank you for your feedback! Most likely you have the formula in a cell within the range you are trying to check. Please try to move the formula beyond the calculated range, it should work.
      If you still see the error, please share a sample spreadsheet with gapps.ablebits@gmail.com, we'll look into it.

  6. 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.:
      =SUMIF(A1:A3,"Felipe",E1:E3)

      Here is a link to your simple example:
      https://goo.gl/jssi6z

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

      • Thanks, Irina!

        I really tried to think of a workaround like you said, but couldn't come up with anything. Yours is a great idea, though. Thank you for that! I think it's gonna work!

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

      • 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

  8. 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 gapps.ablebits@gmail.com, we'll look into it.

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

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

  10. Hi

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

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

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

  11. Hi there,

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

    Thanks!

    Marielle

    • 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        I think I was trying to add too many components!

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

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

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