Count and sum by color in Google Sheets
Function by Color allows you to calculate cells coloured in a certain way in any range in Google Sheets.
Video: How to sum and count colored cells in Google Sheets
Before you start
Note. Go to File > Settings in the Google Sheets menu and see if you have a locale selected there. If not, please set one and confirm your action.
To our regret, Google Sheets takes some time calculating custom formulas. If you have a large table, please wait a few seconds before the value of the built formula changes from "0" to a correct one.
The Refresh results option may also work a bit slow since it recalculates all custom formulas created by the tool in the current tab.
How to count and sum values by fill and font color
Start Function by Color
Go to Extensions > Function by Color > Start in the Google Sheets menu to access the utility:
Tip. You will also find the add-on in Power Tools â a collection of all Ablebits add-ons for spreadsheets:
- Open Extensions > Power Tools > Start in the Google Sheets menu:
- Then either click on the tool icon and pick the Function by color option:
Or access the add-on from the Process group:
Count and sum values by one fill and font color
The first tab lets you calculate values based on one selected fill and/or font color:
- Define the range of cells where you want to calculate colored cells.
Tip. Select a range of cells before running the tool and it will be picked up for processing. But if you run the add-on with a single cell being active in the sheet, that cell will be taken as a place for the result.
To change the range, just enter it manually or click the Select range icon. The latter will open a special window allowing you to pick the necessary cells:
Tip. Use the Auto select button for the tool to identify your table (a used range till the first blank row and column) automatically. - Decide on the background and font colors that you want to calculate:
- Click the Color picker button and you will get a pop-up window inviting you to select a pattern cell from your sheet:
Just click the cell of interest in your table and then hit OK.
- Once the pattern cell is chosen, the Font color and Background color icons in the add-on will feature the corresponding hues.
The checkboxes next to these icons let you decide if you want to calculate cells that share Font color only, or Background color only, or both hues.
Tip. You can change these text and fill colors or set them from the beginning. Just click the corresponding icon and either pick the required shade from the palette or enter its hex code in a special field:
- Click the Color picker button and you will get a pop-up window inviting you to select a pattern cell from your sheet:
- Single out the function you want to use for calculations from the Use function drop-down list. You can choose between the following functions: SUM, COUNT (to count numeric values only), COUNTA (to count both numeric and textual values), COUNTBLANK, AVERAGE, AVERAGEA, MIN, MAX, PRODUCT, MODE, STDEV, VAR, MEDIAN.
Tip. If your task is to count cells with a particular color, it is best to choose COUNTA as it is the only function that works with text values.
- Select or enter the destination cell for the Function by Color formula in the Paste results to field. You can select the top-left cell for the results and the add-on will automatically adjust the range based on the next option.
- Use the Calculate in list to do one of the following:
- insert one formula for the entire range:
- calculate each column individually:
- count colored cells in each row in the range respectively:
Tip. This way, if you want to check how many green cells there are in each row, you won't have to copy the formula manually.
- insert one formula for the entire range:
- Tick off the last checkbox to have the add-on fill results with your pattern colors (hues that are used for calculations).
- Here you can learn more about the additional custom functions that come along with the tool. Use them in your own formulas whenever the color of the cells matters. Jump to this section to get to know them.
- If you don't use the last two options often (Calculate in and Fill results with your pattern colors), click the icon with the upward pointing arrow to hide them. You can make them visible again at any time by clicking the arrow once more.
Hit Insert function to calculate all cells in the desired range based on the selected colors.
Note. To our regret, Google Sheets takes some time calculating custom formulas. If you have a large table, please wait a few seconds before the value of the built formula changes from "0" to a correct one.
Count and sum by all fill or font colors in the range
The second tab calculates cells of all colors that exist in your selected range:
- Identify the range where you want to process colored cells.
Tip.If you select it in the table before running the tool, the add-on will pick it up automatically. If only one cell is selected upon running the extension, that cell will be taken as a place for the result.
You can change the range by typing it manually directly into the field. Or click the Select range icon and pick the required cells from this special window:
Tip. Hit Auto select and the add-on will automatically recognize your used range: all data till the first empty row & column. - Specify whether you want to calculate cells by their font or background colors.
- Pick one of 13 aggregate functions for calculations: SUM, COUNT (to count numeric values only), COUNTA (to count both numeric and textual values), COUNTBLANK, AVERAGE, AVERAGEA, MIN, MAX, PRODUCT, MODE, STDEV, VAR, MEDIAN.
- Choose cells where you want to get the result.
Tip. Select the top-left cell for the results and the add-on will automatically adjust the range based on the next option.
- The Calculate in option lets you tweak where to sum and count colored cells exactly:
- calculate the entire range with a single formula:
- process cells in each column separately:
- or handle colored cells in each individual row:
This is very helpful since you won't have to copy the formula to sum or count colored cells in each column/row respectively.
- calculate the entire range with a single formula:
- If you want to process colored cells in some other formulas, e.g. IF(S), SUMIF(S), etc. make use of these extra custom functions, they are built into the tool. Learn more about them in this section.
- If the last option doesn't really matter, you can hide it along with the info about those extra functions using the upward-pointing arrow icon. No worries, you can make them visible again at any time by clicking the arrow once more.
Hit Insert function to calculate cells of all colors in the selected range.
Note. Google Sheets takes some time to calculate custom formulas. If you have a large table, please wait a bit till the result appears.
How to update the results
Note. By default, in Google Sheets changes of the background color are not considered as a reason to update formula results. Unfortunately, we can't control or bypass that.
If there are any changes only to the formatting in the range, there are 4 ways to update calculations:
- Right from the add-on, using the Refresh results button at the bottom:
- From the Google Sheets menu:
- For the standalone Function by Color, it's Extensions > Function by Color > Refresh results:
- For Power Tools, it's Extensions > Power Tools > Refresh results for Function by color:
- For the standalone Function by Color, it's Extensions > Function by Color > Refresh results:
- In Power Tools, also in the Smart Toolbar drop-down list:
Note. The Refresh results option may work a bit slow since it recalculates all custom formulas created by the tool in the current tab. - One more way to make the formulas recalculate themselves is to change one of the values in the calculated range, e.g. add or remove a character.
How to edit existing formulas
The add-on offers an opportunity to quickly edit existing formulas: change colors and a way to calculate, pick another function or even source range.
Just select a cell with a formula you want to adjust and click the corresponding option from the Function by Color menu: Extensions > Function by Color > Edit selected formula.
Tip. If you use Power Tools, you will find the option in the drop-down list on the Smart Toolbar:
The add-on pane will open, featuring all settings that were used to build this formula. Tweak whatever seems necessary and hit Insert function to get the updated result in the same cell.
Additional custom functions embedded into Function by Color
Besides the formula that our Function by Color creates for you, there are 2 extra functions available:
Not only do they work with colors, but also are useful for SUMIF(S) and COUNTIF(S).
Note. For custom functions to work, you need to have Function by Color or Power Tools installed. The same goes for other users you share your spreadsheets with: for them to see the results, they need to have Function by Color or Power Tools installed as well.
Video: New custom functions — CELLCOLOR & VALUESBYCOLORALL
Tip. Make yourself a copy of the file from the video to follow the tutorial along.
CELLCOLOR
Returns hex codes or the names of colors (per Google Sheets color palette) used in each cell of the range.
Syntax
CELLCOLOR(range, [color_source], [color_name])
- range — The range to check for colors.
-
color_source — [optional — "font" by default] — The source of the colors to check:
- "fill" gets the background color of each cell in the range.
- "font" (default) gets the text color of each cell.
- "both" returns font and fill colors for each cell, for example, '#ffffff, #f4cccc' or 'white, light red 3'.
- color_name — [optional — TRUE by default] — TRUE or FALSE indicating whether to return color names (per Google Sheets color palette) or their hex codes.
- TRUE returns color names, for example, 'light red 3'.
- FALSE returns hex codes, for example, '#f4cccc'.
Sample Usage
- Return fill colors for column Profit:
=CELLCOLOR(C2:C10,"fill",TRUE)
- Pay a 10% bonus for all deals colored green:
=IF(CELLCOLOR(C3,"fill",TRUE)="light green 3",0.1,"")
- Count green cells from column C belonging to Leela in column A:
=COUNTIFS(A2:A10,"Leela",CELLCOLOR(C2:C10,"fill",TRUE),"light green 3")
Notes
- When embedding CELLCOLOR in SUMIF(S), make sure to use it only as a criteria_range. Due to the SUMIF(S) requirements, it will take no custom function as a sum_range and such formulas will return errors.
- For the function to work, you need to have Function by Color or Power Tools installed.
VALUESBYCOLORALL
Returns the entire range where only cells with the same fill and font colors contain values, while other cells remain empty. Can be used as a criteria_range in SUMIF(S), range in COUNTIF(S).
Syntax
VALUESBYCOLORALL(fill_color, font_color, range)
- fill_color — hex code or color name (per Google Sheets color palette) for a background color.
Tip. Use a pair of double quotes "" to ignore fill color:
=VALUESBYCOLORALL("light green 3","",B2:B10) - font_color — hex code or color name (per Google Sheets color palette) for a font color.
Tip. Use a pair of double quotes "" to ignore font color:
=VALUESBYCOLORALL("","black",B2:B10) - range — The range to check for cells within.
Sample Usage
- =VALUESBYCOLORALL("#b7e1cd", "#000000", B2:B20)
or
=VALUESBYCOLORALL("light green 3","black",B2:B10)
Tip. If either font or fill color is irrelevant, enter double quotes instead to ignore them:
=VALUESBYCOLORALL("light green 3","",C2:C10)
- Sum numbers from green cells belonging to Leela from column A:
=SUMIFS(C2:C10,A2:A10,"Leela",VALUESBYCOLORALL("light green 3","black",C2:C10),"<>")
Notes
- When embedding VALUESBYCOLORALL in SUMIF(S), make sure to use it only as a criteria_range. Due to the SUMIF(S) requirements, it will take no custom function as a sum_range and such formulas will return errors.
- For the function to work, you need to have Function by Color or Power Tools installed.
Responses
victoria Murray says:
August 15, 2024 at 10:50 am
Hello,
I was using the function by color add on before I subscribed and it was working perfectly fine. I have now subscribed and it isn't working. I keep getting the error message 'Unknown function: 'valuesByColor'.'
Dear Victoria,
If you see #NAME in the cell itself, this means that the solution below should work. We'd appreciate if you follow this link and click +1 to the reported issue so that Google see how many users have been affected.
1 - Please check if locale is set in the spreadsheet, as a lack of locale may prevent the function from calculating the results as it doesn't know what delimiter should be used. Please go to File > Settings and see if you have a locale selected there.
If not, please set the locale and confirm your action, then select the Refresh results under the Function by color in Power Tools. The function should work correctly then.
2 - There could also be an issue with the permissions. Please check if you are the owner of the spreadsheet. If not, the owner of the spreadsheet needs to have Power Tools or Function by Color installed, please let them try to start the add-on and click Refresh results if this is the case.
If these steps don't help, please email us at support@ablebits.com for further investigation.
Roni Gross says:
December 20, 2023 at 3:27 pm
I copied a file in Google Sheets, but the formulas are not working. Keep getting #Name? errors.
I even deleted the formula and inserted again, and same error.
I even tried to insert a new formula in a new cell, and same error.
Please help
Hello Roni,
Sorry to hear that the formulas do not work.
Please check out the recommendations from this troubleshooting page:
https://www.ablebits.com/docs/google-sheets-add-ons-known-issues/#sum-by-color
Hope this will help.
Kay says:
July 12, 2023 at 9:29 am
How do you sync any changes, If I change a colour on the table is does not automatically change the count, especially if I change a lot of cells to no fill, it does register that the changes have occurred.
Is there a sync button? I have the formula copied to the sheet
Thank you
Hello Kay,
Thank you for your question.
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 results option for our Function by color tool:
https://www.ablebits.com/docs/google-sheets-count-sum-colors/#update-results
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.
If you have any other questions, please do not hesitate to ask.
Malcolm says:
June 24, 2023 at 6:23 pm
I keep getting hyphens "-" in the result. why?
Hello Malcolm,
Thank you for your question. For us to be able to understand what is causing the issue, please click on the cell with a hyphen and take a screenshot showing the cell itself along with the content of the formula bar, please also take a screenshot of the options you select in the add-on, then send the screenshots at support@ablebits.com.
We'll do our best to help.
farrah says:
June 8, 2023 at 10:40 am
Hi, how to solve this
Rate by color
If cell is red = 2
if cell is orange = 3
if cell is green = 4
can we Identify by colors? Thank you
Hi Farrah,
Thank you for your comment. Please have a look at this example described on this help page.
Also, this article on our blog may be helpful to, too.
Mary-Lawrence Porter says:
March 16, 2023 at 10:48 pm
Why when I want to enter a formula I get this message: ''Please select a range with data and try again.''?
Hello Mary-Lawrence,
Please send us the screenshots showing your data and the options you choose to support@ablebits.com. We will do our best to help.
Victor Osinskiy says:
December 15, 2022 at 7:31 am
Hi.
I only need this addon "Count and sum by color".
How can I pay only for it ?
Hi Victor,
Feel free to buy a subscription for Function by Color using one of the links below:
- 12-month plan: https://secure.2checkout.com/order/checkout.php?PRODS=31468839&QTY=1&CART=1&CARD=2&DESIGN_TYPE=2&SHORT_FORM=1&COUPON=TrSbrExp-MnrAdns-01&CLEAN_CART=ALL&SRC=email
- lifetime plan: https://secure.2checkout.com/order/checkout.php?PRODS=31468973&QTY=1&CART=1&CARD=2&DESIGN_TYPE=2&SHORT_FORM=1&CLEAN_CART=ALL&SRC=email
tIM says:
July 4, 2022 at 3:28 pm
Can this be used to count for different colors and display results for each color>
Hello Tim,
Thank you for your question.
Sorry, there is no way to create one formula with more than one color by the add-on at the moment. You need to enter a separate formula for each color you want to count.
Our developers will consider embedding this possibility in one of the future versions of the add-on. Thank you.
Hello Tim,
I'm happy to let you know that we've implemented this feature. There's now an additional tab that will calculate all fill or font colors in the selected range for you with just one formula. Please read here for more details.
Sierra says:
March 9, 2022 at 9:23 pm
I have followed the instructions, but the total in the box only says "1", even though there are definitely more than one cells with a particular color.
How can I fix this?
Sierra says:
March 9, 2022 at 10:16 pm
Never mind, I figured it out.
Thank you.
Thank you for the update, Sierra.
Fail says:
March 12, 2022 at 10:18 am
hi can you please detail how you figured this out thank you
Lisa Summitt says:
March 2, 2022 at 8:53 pm
Is this a Freemuim app? Do they charge after 30 days?
Hello Lisa,
Thank you for your question. Sorry, this is not a Freemuim app. Please keep in mind that once your 30-day trial period expires, you'll be able to use the add-on just once a day.
However, a 12-month subscription is at a special offer now (30% off). It costs USD 12.00 (one-time payment), if you decide to go for it just send us a message at support@ablebits.com.
Rowan Shead says:
August 10, 2021 at 2:20 am
Can this function be used to count different coloured Checkboxes
Hello Rowan,
Thank you for your question.
Yes, the add-on can count colored checkboxes (the color can be set using the Font color option). The tool will count cells with font color only provided that you select the COUNTA function. Don't hesitate to contact me again if you have any other questions or difficulties.
Adi says:
April 17, 2021 at 1:03 pm
Can valuesByColor be combined with another criteria? I need to sum by color and by a particular item at the same time.
=SUM(valuesByColor("#6aa84f","#000000",A$7:A$12)) and another criteria =sumif(B7:B12,B7,A7:A12)
Hello Adi,
Thank you for contacting us.
Unfortunately, since our valuesByColor returns values ââfrom cells, it cannot be wrapped in another condition for another column. We'll make a note of this case as a potential improvement and will see if it's technically possible to implement something like this in the future.
For now, I can only offer to try and use Scripts. Here's an overview of Google Apps Script with a lot of helpful content and links:
https://developers.google.com/apps-script/overview
I wish I could help you better.
Daniel says:
November 4, 2020 at 3:14 am
Hi, would it be possible to extend the sum colours feature to allow a choice of using the colour of a cell, so the function then instead of hard-coding the colour uses whatever the colour is of that selected cell.
This would allow greater flexibility if you decide to replace a colour across the sheet.
Hi Daniel,
Thank you for your comment. First off, it is possible to update the formulas created by the add-on. You may either edit a formula in the add-on or manually enter the name of the color from the Google Sheets color palette and the change will be applied.
If this is not exactly what you need, please clarify. If you send us any screenshots illustrating your scenario to support@ablebits.com, it'll be very helpful. Thank you!
sunks says:
November 10, 2020 at 1:55 pm
I start using the app today but it is one of the easy and must well develop for all to use.
Thanks so much
Very easy to understand.
The Gambia, West Africa.
Please try it
Hello Sunks,
Thank you so much for your feedback! We're happy to hear you enjoy using our software.
If it's not too much trouble for you, we would appreciate it if you rate our add-on and write a few words on Google Workspace Marketplace. Your positive feedback motivates us much better than sales numbers. :)
Thank you in advance and have a great day!
Mo says:
September 20, 2020 at 4:32 am
Hi,
I have been able to successfully use function by color to count how many cells are a certain color, but is it now possible to take it a step further and count only cells that have a certain string of text in them? For example if I have 20 green cells, 3 of them have the word "Sale" in them, while the other 17 have other words. Is it possible to use the countif function together with the backgroundcolor function to count how many cells have the word Sale in them? If so how can I nest the two functions correctly?
Mo says:
September 20, 2020 at 5:15 am
Ok scratch that, I've figured out how to do it. Now for the even next step, is there a way to create a list that shows me how often each word was mentioned in all the cells with a green background color?
Hi Mo,
Sorry, the add-on can't help with this task, however, you can have a look at this blog article and find a solution there:
https://www.ablebits.com/office-addins-blog/countif-google-sheets/#countifs
Jack says:
September 24, 2021 at 5:20 pm
Mo, would you be able to share how you did this
Kat says:
May 17, 2020 at 3:38 pm
Hi there,
I see the same question I want to asked has already been asked in August 2019 (See below)
Could you tell me if this has been solved yet as I would really like to count more than one color.
I hope to hear from you soon!
Thanks,
Kat
YATIN KUMAR says:
August 6, 2019 at 8:22 am
How to do it for multiple colors?
Reply
Natalia Sharashova (Ablebits.com Team) says:
August 6, 2019 at 8:32 am
Thank you for your question, Yatin.
We haven't provided for a way to process more than one colour at a time, so you need to enter one formula for each colour you want to count.
We will consider embedding this possibility in one of the future versions of the add-on.
Hi Kat,
Thank you for contacting us. I'm really sorry but our development team is still working on this feature. We will let you know once it is available. Our apologies for any inconvenience.
Hi Kat,
The recent upgrade of the Function by Color tool lets you process more than one color in one formula. You will still need to create separate formulas for each color, but you can then combine these formulas into a bigger one, like SUM, or COUNT(A), or AVERAGE, etc. and they will work.
anurag says:
April 10, 2020 at 9:07 am
i just want to count the coloured cells, even if they are empty. This formula doesn't allow me to do so.
anurag says:
April 10, 2020 at 11:14 am
its sorted, i can use COUNTA()
Hello Anurag,
We are glad to hear that you have sorted out the problem.
If there are colored cells with some data and empty colored cells in the selected range and you want to count all of them, the COUNTA function is the best option in this case.
Rik says:
January 28, 2020 at 11:50 am
Hi,
I installed power tools, but when I try to use it on my spreadsheet it doesn't allow to click on it?
I selected the locale and restarted my computer. What am I missing?
Thank you
Hi Rick,
Thank you for your interest in our product.
It looks like add-ons from G Suite Marketplace are prohibited by your organization on the domain level. Please contact your administrator. He/she should be able to check this in the Google Admin console and whitelist the tool for you or all users if possible.
Pat says:
January 3, 2020 at 8:05 am
Hi,
I'm getting #NAME? Error Unknown function: 'valuesByColor'. I already set the locale (Thailand) but it's still not working.
Hi Pat,
Thank you for contacting us. This problem may be caused by an issue with the way permissions are handled when you use more than one account in Google Sheets. Signing out of all other accounts and logging in just to the account with the subscription should fix this. If you share documents between the accounts, please make sure it is used under the necessary account.
If not, please select the Refresh results under the Function by color in Power Tools. The function should work correctly then.
Mark Bratt says:
December 29, 2019 at 9:03 pm
Hi,
I'm getting Unknown function: 'valuesByColor'. Does this function work when locale is set to United Kingdom? I'm actually trying to add cells that are not filled ie =SUM(valuesByColor("#ffffff", "#000000", 'Revolut Transactions'!G124:G129)).
Hi Mark,
Thank you for contacting us.
We have just replied to you via email. Please check your Spam/Junk/Trash email folders if you still donât see our email in your Inbox. Thank you.
Mary says:
December 12, 2019 at 8:43 pm
Hello!
I am having and issue with Power Tools. I need to get the total of each color in the range but I end up getting a '1' value.
Thanks!
Katerina Bespalaya (Ablebits Team) says:
December 13, 2019 at 1:27 pm
Hello Mary,
Please note that our tool can't process more than one color at a time, so you need to enter one formula for each color you want to count.
Besides, 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. Additionally, our tool doesn't support the Theme colors which is a relatively new formatting tool in Google Sheets.
If the locale is set and the the standard Fill colors are used in your spreadsheet, then please contact support@ablebits.com for further assistance.
Karolina says:
December 3, 2019 at 6:04 pm
Hi,
I try to use is to sum values in cells marked green, but it gives me #NAME? error saying that valuesByColor function is unknown. Any ideas how to fix it?
Than you!
Katerina Bespalaya (Ablebits Team) says:
December 3, 2019 at 7:35 pm
Hi Karolina,
I've just sent you an email with the details we need to understand the problem better. Please provide us with the information requested in the email. Thank you.
Karolina says:
January 8, 2020 at 10:15 am
I have the same problem, please let me know how to solve it
Hello Karolina,
Thank you for your comment. This problem may be caused by an issue with the way permissions are handled when you use more than one account in Google Sheets. Signing out of all other accounts and logging in just to the account with the subscription should fix this. If you share documents between the accounts, please make sure it is used under the necessary account.
Also, please keep in mind that 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 select the Refresh results under the Function by color in Power Tools to check if the function works correctly.
Toya says:
November 1, 2019 at 12:18 pm
This also gives me a value of 0 when searching for the green color.
See code: =COUNT(valuesByColor("#b7e1cd","#000000",'Dinner Guests'!F2:F49))
Why is this happening?
Katerina Bespalaya (Ablebits Team) says:
November 1, 2019 at 5:43 pm
Hello,
We have just replied to your question by email. Please check your Inbox. Thank you.
Karina Bret says:
October 10, 2019 at 6:09 am
Don't know why, but I use the formula to counta cells in a green color (defined by conditional formatting) and referred to a cell that is not conditional formatted but using the same background color I want to count, and regardless of the cells colors in my row, the result is always "1" for counta and 0 for count. Why is that ?
Thank you for contacting us, Karina.
First, due to an issue on the side of Google, please make sure to sign out of all other Google accounts you may be logged in to.
Then, 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 option next to Function by color in Power Tools. The function should work correctly then.
Please let us know if you still have any difficulties.
thanaphon says:
August 6, 2019 at 3:06 pm
thank you very much.
YATIN KUMAR says:
August 6, 2019 at 8:22 am
How to do it for multiple colors?
Thank you for your question, Yatin.
We haven't provided for a way to process more than one colour at a time, so you need to enter one formula for each colour you want to count.
We will consider embedding this possibility in one of the future versions of the add-on.
Hello Yatin,
The recent upgrade of the Function by Color tool lets you process more than one color in one formula. You will still need to create separate formulas for each color, but you can then combine these formulas into a bigger one, like SUM, or COUNT(A), or AVERAGE, etc. and they will work.
Rami says:
June 20, 2019 at 6:41 pm
Hello team
kindly i'm looking to do the following:
1- i have a cell which has a red background
2- i Have an empty cell next to it
3- i need to have a number (any number) to get filled in the empty cell if the background color is red and zero if it's blank background
how can i do that :)
Hello Rami,
Thank you for contacting us.
Unfortunately, we do not have an add-on that can help you with your task. Sorry for not being able to help you better.
Ray says:
May 27, 2019 at 1:01 pm
Hi
See column J, my cells in green are not being summed (although I have selected the pattern colour)
Pls helP!
thanks
Hi Ray,
We replied to you by email, please check your Spam/Junk/Trash email folders if you don't see the message in your Inbox.
Angins says:
March 22, 2019 at 3:55 am
Thank You
You're most welcome. :)
Megan says:
March 5, 2019 at 1:51 am
Is there a way to edit a function by color once it's already been made, or do I have to redo it? If it is possible, what are the steps to do this?
Thank you.
Thank you for your question, Megan.
You can edit this formula like any other formula in Google Sheets - select the cell with it and go to the formula bar to edit it. You can easily change the range and the required sheet name. The formula will recalculate itself accordingly.
As for colors, if you're not sure of their correct hex codes, I'd recommend using the add-on to change them.
Megan says:
March 5, 2019 at 10:56 am
Thanks so much for the quick response. Is it possible to get the box back up that is initially used to create the function to change the RGB codes, or do I need to start over? Thanks again!
Thank you for the idea, Megan. :)
I've just contacted our developers with this suggestion for a discussion. They will see if it's possible to introduce such a feature.
I can't give you any timing, but I can let you know if/when it is implemented.
In the meantime, to change colors correctly, please start the tool to build a new formula.
Hello Megan,
The recent upgrade of the Function by Color features a new option: Edit selected formula. it will help you adjust colors quickly. You can read more about it here.
Carlos says:
February 19, 2019 at 12:40 am
I tried to use it by configure the most basic options, but it doesn't work. it seems bugged and the error is in russian???
Thank you for reporting this problem to us, Carlos.
For us to be able to assist you better, please send us the following details to support@ablebits.com:
- Reproduce the issue. If you get the same error message, please send us its screenshot.
- Describe in detail what steps you follow before the issue occurs. What options do you choose on each step of the add-on? The screenshots of the steps would be very helpful.
This information will help our developer understand what is causing this problem.
Igor says:
December 25, 2018 at 4:23 pm
I haven't found the reason, but in some cases Sum function give an error (#ERROR!).
The reason of the error is two extra "f" in front of first color code.
Please compare:
Error: =SUM(valuesByColor("#ff00ff00"; "#000000"; '1'!A3:D10))
Correct: =SUM(valuesByColor("#00ff00"; "#000000"; '1'!A3:D10))
If you delete extra "f", everything work properly)
Thank you very much for your comments, Igor.
I'm afraid, there's currently an issue that has appeared recently due to some problems on the Google side. For some reason, Google Sheets occasionally stops reading custom formulas made by other add-ons. To our regret, our Sum by Color was also affected, but flipping the ranges for calculation one way and back with our Flip tool usually helps.
However, "ff00ff00" is an incorrect color code. If this code was returned to you by our add-on, please share an example spreadsheet with us (gapps.ablebits@gmail.com) with the colors you were trying to count.
We'll check what may be causing the problem.
Thank you.
Igor says:
December 25, 2018 at 4:06 pm
Hi,
I have found solution)
By some reasons, when you use POWER TOOLS in already formed table, SUM by color function doesn't "catch" cells color in "Source range" (even if cells have the same color and code e.g. #00ff00). After applying the function it give you "0".
You have two options:
1. Apply Google Sheet function "Paint format" from your "Pattern cell" to the cells in your "Source range".
2. Simulteniously select "Pattern cell" and correspondent cells in your "Source range" and apply to them the same color.
Don't forget "Refresh results" in POWER TOOLS after each actions!
After applying POWER TOOLS, all newcreated cells with correspondent color will be "catched".
Alex says:
January 22, 2019 at 6:30 pm
Igor,
I think this is the issue I'm having in my google sheet. Where/how do I find "paint format" exactly?
Thanks
Hello, Alex,
I'm sorry you're having difficulties with the tool.
Would it be possible for you to share your spreadsheet with us (gapps.ablebits@gmail.com) with a description of your task?
We'd look into the issue and do our best to make our add-on work for you.
Thank you.
Tom Arild Raen says:
July 6, 2020 at 3:58 pm
Hi:)
Some of my cells are showing "0" even if its a copy paste of working cells with the exception of being a different column. At first i thought it was the copy paste that wrecked it, but it works for some columns thats the weird thing. Any idea why this is happening?
Hi,
Thank you for your message. Please try to click the Refresh results option. That should do the job. If not, please share a small sample spreadsheet with us (support@apps4gs.com). When sharing, make sure the option 'Notify people' is checked. To grant us access to your data, press the Share button at the upper right corner of Google Sheets and enter support@apps4gs.com.
We keep that Google account for file sharing only and do not monitor its Inbox. Please do not email there.
If you have confidential information in your records, please replace it with some irrelevant data, just keep the format. Thank you.
dan says:
December 5, 2018 at 5:34 pm
it does not work for me
Hello, Dan,
I'm sorry to hear you've encountered difficulties with our tool.
Please email us to support@ablebits.com with the screenshots of your source data and the result you're getting after running the add-on.
We'll investigate the problem.
Thank you.
Hem Kashyap says:
November 29, 2018 at 5:20 pm
I am not able to do . I have 500 cells and I like to count 3 colors used . these are in 7 heads.
Hello, Hem,
Thank you for reporting a problem.
For us to be able to assist you better, please send us the screenshot of how your data is stored and of the result you're getting with our add-on.
Also, if it's possible, please share your spreadsheet with us (gapps.ablebits@gmail.com) with a description of your task. I kindly ask you to shorten the table to 10-20 rows.
We'll look into the problem and do our best to assist you.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.