Sum and count by color in Excel

Count and Sum by Color is a helpful Excel add-in that lets you instantly calculate cells of the same tint. On this page, you will find out how to use the tool to sum and count the colored data in your spreadsheet.

How to count colored cells in Excel

If you work with tables that have color-coded cells, see the details below for a quick way to count them.

Start Sum by Color

Open the Excel worksheet where you need to count cells by color and click the Sum by Color icon in the Calculate group on the Ablebits Tools tab:

Start Sum by Color.

Pick one of two tools in the drop-down list:

Sum and count by one color

Use this utility to calculate cell data based on the selected color.

After picking the first option, you will see the Count and Sum by One Color pane.

How to count and sum by one color in Excel.

  1. By default, the add-in picks the entire data range in your Excel worksheet. You can change it by selecting the needed table or entering the range address manually.
  2. Also, you can get the entire table selected automatically. For this, pick any cell in the table and click the Expand selection icon.
  3. Click the Color Picker icon and select a cell that represents the background and/or font color you want to sum and count by.
  4. Click the Calculate button and get the result on the add-in's pane.
    Note. If there are empty cells in your range, the tool will identify the font and fill colors that are set in the Format Cells option.
  5. To see calculations by background color, go to the Background tab.
  6. If you want to see the count by font color, open the Font tab.
  7. If needed, you can paste a single calculation result to your table: just hover the mouse cursor over the needed function (SUM, COUNT, MAX, etc.) and click the Paste button. The add-in will ask you to pick the top-left cell for the result:

    Paste the result of Sum by One Color calculations.

    Note. If you use this option, the numerical result will be pasted into your table without any text labels. For your convenience, the resulting cell will have the same font/background color as the cells you counted.
  8. Click the Paste All... button to insert all the results to the place you choose in your sheet. You will see the same dialog box to specify the top-left cell for the count color results.
    Note. The results will have text labels and will be colored the way the cells you counted:

    Sum and count by one color and paste the result.

Sum and count by all colors

This tool will give you a quick overview of color-coded data in your table. For example, if you use different hues to highlight prices from the vendors, the utility will sum cells by color and show you the total for any group of numbers.

To count colored cells in Excel, open the worksheet with your data and run Sum and Count by All Colors:

Start Sum by All Colors.

The add-in selects the entire data range in your worksheet automatically. You can change the range by entering the new address manually or selecting it in you sheet.

When the range is chosen, click Calculate and see the results straight on the pane:

Sum and Count in Excel by all colors.

  1. Select Background to see the result counted by the cell's fill color.
  2. Choose Font to total the selected values by their text color.
  3. Open this drop-down menu to switch to the function that interests you most: Sum, Count, Counta, Countcolor, Average, Averagea, Averagecolor, Max, or Min.
    Note. Use the COUNTCOLOR function to get the number of all cells that have the same background or font color. Be aware that COUNT only counts cells containing numbers, COUNTA counts all cells that aren't empty. COUNTCOLOR counts all cells that have the appropriate font or background color and it doesn't matter what is in the cells: text, numbers, or nothing:
    Count all cells with the same background or font color.
    Note. Use the AVERAGECOLOR function to get the average of all cells that have this background or font color, empty as well. AVERAGE returns the average of cells with numbers only, AVERAGEA finds the average of cells that and are not empty, they may include logical values and text representations of numbers. AVERAGECOLOR returns the average of all cells that have this background/font color, whether they contain values or are empty:
    Find average or all cells with the same background of font color.
  4. This handy tool also lets you paste the resulting numbers from its pane to any location in your Excel worksheet. Click Paste All... and you will see a dialog box where you need to specify the top-left cell in your current or new worksheet. Once the cell is chosen, click OK to paste calculation results by the selected function.
    Note. The calculation result is inserted into your worksheet without any text labels. The color of the cell with the result represents all the cells that were counted or summed.

Sum by colors in Excel.

Note. Sum and Count by Color does not identify the format of the values in the cells. For example, if you have dates in your range, the tool will treat them as numbers.


Vedant Pandey says:
July 20, 2018 at 5:55 pm

It was very good and the video showed how to count by color. It was my first time so it was good


Hi Vedant,
thank you for your kind feedback!


Have file with 501 rows and 87 rows - the sum by color tool seems to hang, the file closes abruptly and if reopen again get a message file locked by user even though file closed . on reopening get message that file is locked by user . I need to reboot to work on the original file


Thank you for using Sum by Color and for your message. In order we could solve the problem you face, please contact the support team at and let us know the following information:
1. Please describe in detail the steps you take before your file closes, which actions do you perform?
2. Does this happen only when you use the Sum by Color tool?
3. Please make a screenshot of the message you get when you reopen the file.
We will do your best to help you as soon as possible.
Thank you.


I want to be able to sum all cells adjacent to colored cell? Is that possible? So basically the colored cell tells me which cell to add from.


Hi, Matt,
Thank you for using the Count and Sum by Color utility.
The tool sums cells by color, so you can first color the cells you need to sum and then use the add-in. Unfortunately, there is no way to sum noncolored cells adjacent to colored ones with the help of Count and Sum by Color.
Thank you.

Jeffrey Scott says:
February 2, 2019 at 3:48 pm

Ok I have columns with color data. I want to count the number of cells that are green, etc and put these totals at the bottom of the worksheet. I want this to be shown as a formula so that I do not have to write it for the over 100 columns of data.


Hi Jeffrey,
Thank you for your interest in our product. To my regret, the Count and Sum by Color tool does not paste the results of the calculations as formulas, only as values. But I want to thank you for this idea, we will think of ways to implement this feature in our future releases.
Please feel free to contact us with any other questions!


In normal Excel formulae,
you can have the = button and insert the cells you need to calculate. So that even when the value in the cells selected changes, the = value will update according.
My issue now is that although the tool is great, it doesn't provide this flexibility. I am designing a spreadsheet with the goal of allowing my Finance manager to autonomously use the spreadsheet designed without any interference from me.
The calculation has to be done manually. Is there a way I can just insert the sum function and my finance dept can key-in the value and the = value automatically changes?


Hi Alvin J,
Thank you for your feedback on the Count and Sum by Color utility. I understand your task, but there is no such functionality in the tool to automatically recalculate colored cells. However, we will think of ways to implement it in our future releases.
Thank you.


I need the combination of what Alvin and Jeffrey said.
Can you say when this will be available?


Hi Maurice,
Thank you for the comment. We have looked into these requests, and we do not plan to implement such features yet. The thing is that these functionalities will require to have Ultimate Suite installed on all machines on which you open workbooks with such functions.
Thank you.


Hello Irina,

So what you're saying that if we buy the Ultimate Suite we will be able to Sum by Color as a formula within the workbook and not have to do it cell by cell?

If this is the case, please let us know, since it is a function that, I for one, am very interested in.


Hello Jose,
Thank you for your question.

Our support team assistant has just replied to you via an email. Please check your Inbox for our email. Thank you.

Dries Cronje says:
July 4, 2019 at 11:03 am

I want to sum all cells with background color yellow

Katerina Bespalaya says:
July 5, 2019 at 10:43 am

Hello Dries,

If your task is only to sum the cells with the background colored in yellow, please note that our tool will perform all the calculations, but after that you can paste just the summing result in your table. See this part of the instructions above.

Count and Sum by Color comes as a part of our Ultimate Suite for Excel. You can install its fully functional trial version and test the tool out during 30 days for free. Here is the direct download link.

If you have any questions or need further assistance with the add-in, please email to


How do I get it to re-calculate each time values are changed in the range?
And how do I copy this formula to each different column if I only want the sum of that column?


Hello Ada,

You see, the add-in returns the result as a value, not a formula. To recalculate everything, you will have to run the add-in anew.
Hence, for each new column, you will also have to select it and calculate the results separately.

I will forward these feature requests to our developers for further consideration.
I sincerely apologize for any inconvenience.


i have a file that has colored rows (same color) while some of the rows are not colored, i would like to know the formula to know how many rows has the color


Hello Usman,

Thank you for your comment. Our Count and Sum by Color tool will count the colored cells, not rows. As a workaround, you may select one of the rows and count the colored cells there to find out the number of colored rows in your sheet.

If however, you want to know more about Excel formulas and functions, please have a look at our blog:

Andrew Walker says:
June 26, 2020 at 11:37 pm

Have you implemented a way to have "sum by color" auto calculate when the cells are changed?



Hello Andrew,

Thank you for your comment. Sorry, this functionality is unavailable yet. The current version of the add-in pastes the result as a value, not a formula so if you need to recalculate it, you will have to run the add-in again.

The recalculate feature is in our developers' roadmap, but we cannot give any timing in its release yet. Sorry.


Dear All,

I try to download and very helpful, but after that my Whole PC auto format and lost all the document



Thank you for your message. I have just replied to you by email.


I have discussed the issue you have faced with our technical specialists and would like to confirm that our Ultimate Suite works only when Excel is running and cannot be the cause of the problem.

can I sum all colored column in workbook, not just 1 work sheet? Thank you


Hello Diana,

Thank you for your question. Sorry, the add-in can sum colored cells only within 1 Excel worksheet. Thank you.


I need to count cells with color, without numerical value in the cells.

example: if the cells have red colors, then i can get the total of cells with red colors


Hello Raul,

Thank you for your comment. The total number of red cells (both with values and without them) will be displayed next to COUNTCOLOR on the add-in pane. If that is not what you need, please feel free to email us at Any screenshots illustrating your task and the expected result will be appreciated in this case.


Ask a question (posted publicly)

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