## How to count or sum cells based on the font colors in Excel?

How could you get the number of cells or sum all the cells which contain a particular font color in Excel? For example, I have a range of data in a worksheet as following screenshot shown, and now I want to respectively count or sum the cells that have a red, blue and black font color. Normally, there is no direct way to deal with this task, here, I will talk about some tricks for soling this job.

Count or sum cells based on font colors with User Defined Function

Count or sum cells based on font color with some useful functions

#### Count or sum cells based on font colors with User Defined Function

###### Count cells based on font colors:

To calculate the number of cells with specific font colors, please do as these:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Count cells based on font colors:

``````Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
'Update by Extendoffice
Application.Volatile
Dim rng As Range
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
CountColour = CountColour + 1
End If
Next
End Function
``````

3. Then save this code and go back to the worksheet, and then enter this formula in to a blank cell =CountColour(A1:D10,A2) , see screenshot:

Note: In the above formula, A1:D10 is the range that you want to use and A2 is the cell with a particular font color that you want to count.

4. After typing the formula, press Enter key, and you will get the number of cells with red font colors. If you want to count other font colored cells, please repeatedly enter the formula as possible as you need. You will get the following results:

###### Sum cells based on font colors:

To sum cells based on font colors, the following User Defined Function can help you.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Sum cells based on font colors:

``````Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
'Update by Extendoffice
Application.Volatile
Dim rng As Range
Dim xTotal As Double
xTotal = 0
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
xTotal = xTotal + rng.Value
End If
Next
SumByColor = xTotal
End Function
``````

3. Then save this code and return to the original worksheet, and then enter this formula =SumByColor(A1:D8,A1) into a blank cell, see screenshot:

Note: In the above formula, A1:D10 is the range that you want to use and A2 is the cell with a particular font color that you want to sum.

4. Then press Enter key, and you will add up all cells with red font colors. If you want to sum other font colored cells, please repeatedly enter the formula. You will get the following result:

#### Count or sum cells based on font color with some useful functions

May be the User Defined Function is troublesome for you to save and apply, here, I will recommend you a handy tool-Kutools for Excel, with its advanced functions, you can solve this task quickly and easily.

Tips:To apply this COUNTBYFONTCOLOR and SUMBYFONTCOLOR features, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

###### Count cells based on font colors:

1. Click a cell where you want to put the count result, and then click Kutools > Kutools Functions > Statistical & Math > COUNTBYFONTCOLOR,see screenshot:

2. In the Function Arguments dialog box, specify the data range and color index cell that you want to count by font color, see screenshot:

3. And then, click OK button, you will get the first count result, to get other results, you just need to copy this formula and change the cell references to your need. See screenshot:

###### Sum cells based on font colors:

1. Click a cell where you want to put the count result, and then click Kutools > Kutools Functions > Statistical & Math > SUMBYFONTCOLOR,see screenshot:

2. In the Function Arguments dialog box, specify the data range and color index cell that you want to sum by font color, see screenshot:

3. And then, click OK button, you will get the first sum result, to get other results, you just need to copy this formula and change the cell references to your need. See screenshot:

#### Count or sum cells based on font color with an amazing feature-Count by Color

Kutools for Excel also provides an easy feature- Count by Color, with this utility, you can quickly get the calculation result such as count, sum, average cells and so on by the background color, font color, conditional formatting as you need.

Tips:To apply this Count by Color feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Select the data range that you want to count and sum based on different colors.

2. Click Kutools Plus> Count by Color, see screenshot:

3. In the Count by Color dialog box, choose Standard formatting from the Color method drop down, and select Font under the Count type drop down, and the cells with same font colors have been counted, added up, averaged and so on, see screenshot:

4. And then click Generate report button, you will get the statistics in a new workbook. See screenshot:

#### More articles:

• Count And Sum Cells Based On Background Color In Excel
• Supposing you have a range of cells with different background colors, such as red, green, blue and so on, but now you need to count how many cells in that range have a certain background color and sum the colored cells with the same certain color. In Excel, there is no direct formula to calculate Sum and Count of color cells, here I will introduce you some ways to solve this problem.
• Sum / Count Bold Numbers In A Range Of Cells In Excel
• When you have a data range which contains some bold numbers in a worksheet, and now you want to sum or count only the bold cells, of course you can add them up one by one manually, but it will be time-consuming. How could you sum or count only the bold cells in Excel with an easy and quick way?
• Apply Color Gradient Across Multiple Cells
• In Excel, we can easily fill background color to a cell or multiple cells, but, sometimes, we need the color be filled gradient as following screenshot shown, how could get the color gradient in a cell or across multiple cells in Excel?
• Concatenate Cell Columns And Keep Text Color In Excel
• As we all known, while concatenating or combining cell columns into one column, the cell formatting (such as text font color, number formatting, etc) will be lost. This article, I will introduce some tricks to combine the cell columns into one and keep the text color as easily as possible in Excel.

#### Count or sum cells based on font / background / conditional formatting color:

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

### Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

 Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Spreadsheets: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi..

I used it to count and sum from matrix. The problem is that the I need to count/sum in multiple range of cells. Is it possible to update this code to do this?
This comment was minimized by the moderator on the site
Hello, Joseph,

Maybe there is no direct way for you to count or sum the cell values based on font color in multiple ranges, but, you can apply the third method in this article and use Count by Color feature of Kutools for Excel, with this feature, you just need to select the multiple ranges first, and then apply the feature.
https://www.extendoffice.com/documents/excel/1418-excel-count-sum-by-font-color.html#a3

This comment was minimized by the moderator on the site
COUNT CELLS BY FONT COLOR tutorial was great! But it is not working when the font color was based on the conditional formatting. Do you have something for this concern?
This comment was minimized by the moderator on the site
Hello,
The codes in this article can not support the conditional formatting, but, you can use our Kutools for Excel's Count by Color feature, it can help you to count or sum conditional font colors. Please try it.
This comment was minimized by the moderator on the site
This was exactly what I needed - Thanks!
This comment was minimized by the moderator on the site
= SumByColor (A1: D8, A1) NÃO FUNCIONOU

=SumByColor(A1:D8;A1) FUNCIONOU

TIRANDO ESPAÇO E USANDO " ; " AO INVÉS DE " , " AI FUNCIONOU LEGAL.

USO EXCEL 10

MUITO BOM.

This comment was minimized by the moderator on the site
Is there a way to change Count Or Sum Cells Based On Font Colors With User Defined Function into count or sum cells based on de conditional formating?

I've tried with

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double

Application.Volatile
Dim rng As Range
For Each rng In pRange1

If rng.FormatConditions.Font.Color = pRange2.FormatConditions.Font.Color Then
CountColour = CountColour + 1

End If
Next
End Function

But it appears not to work, any suggestion?
Thanks
This comment was minimized by the moderator on the site
Hello, Borja,
May be you can use our product, Kutools for Excel, with its Count by Color feature, you can quickly count or sum the cell values based on the conditional formatting without any VBA code, please try. You can download it and free trial in 60 day.
Thank you!
This comment was minimized by the moderator on the site
I tried the SumByColor. It works, but not if the numbers have been coloured by conditional formatting. For example, I have a list of different numbers that are coloured red if they are within a range set by a conditional formatting rule. I would then like to sum only the red-coloured numbers. But the SumByColor VBA code does not work in this situation. Any suggestions to make it work. Thanks
This comment was minimized by the moderator on the site
Hello, Xiahui,
The above code can not applied to conditional formatting color, but, you can use our Kutools for Excel' Count by color feature, with it, you can quickly get the result for counting or summing based on the conditional formatting color. See the below screenshot:
This comment was minimized by the moderator on the site
Hey
Nice macro. I used it to just count from a simple matrix. The problem is that the macro counts also empty cells. How to exclude empty cells from counting?
This comment was minimized by the moderator on the site
Hello, Kriss,
To count the cells based on font color excluding the blank cells, please apply the below User Defined Function, please try:

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
Application.Volatile
Dim rng As Range
For Each rng In pRange1
If rng.Value <> "" Then
If rng.Font.Color = pRange2.Font.Color Then
CountColour = CountColour + 1
End If
End If
Next
End Function
This comment was minimized by the moderator on the site
Exactly what I was looking for! This will save me lots of time. Thank You
This comment was minimized by the moderator on the site
Muito obrigada já ajudou muito, porém a minha formula só adiciona à soma outro número quando eu uso o pincel para mudar a cor, se eu trocar a cor da fonte pela barra de ferramenta não dá certo, alguém saber me explicar -
This comment was minimized by the moderator on the site
This was SO helpful - Thanks very much!
This comment was minimized by the moderator on the site
Hướng dẫn bên trên hình như chỉ áp dụng khi màu của giá trị trong ô hoặc màu nền của ô được tô màu bằng Manual.M muốn đếm số ô mà giá trị được bôi màu theo cách Conditional Formatting.Xin giúp đỡ ạ!
This comment was minimized by the moderator on the site
Hi I tried it but when I changed the color of other items, it doesnt add up or the sum doesnt change.
This comment was minimized by the moderator on the site
Hi,

I followed the instructions and it works well. But then when i re-open the file, the module window is empty. It seems i cannot save the code as needed. A box pops up saying that "VB project cannot be saved in macro-free workbooks" and therefore i have to save as a macro-free workbook.

Thanks,
J. Wong
This comment was minimized by the moderator on the site
Rather than count or sum cells based on font colour, is there a way to display certain 'values' in another column based on font colour? for example, I have a column with multiple company names that each have a specific font colour to indicate a specific calendar year. I would like to simply create a 'year' column beside this. So for example if column B contains text with font colour green, this will display "2017" in column C. And if column B contains text with font colour red, this will display "2016" in column C. I dread to do this manually as I have thousands of rows!
This comment was minimized by the moderator on the site
Hi,
I followed the instructions above for SumByColor and they worked great.
I was wondering whether there is a similar formula in VB to sum by background color rather than using Kutools.
thanks, Antonella
This comment was minimized by the moderator on the site
Hi, Antonella,
https://www.extendoffice.com/documents/excel/1155-excel-count-sum-cells-by-color.html

Thank you!
This comment was minimized by the moderator on the site
hi, your solution works well but when i change the content of a cell in the range to the colour selected the result cell doesn't auto update unless i go to the result cell select the formula and hit enter. Is there a way to auto update the result cell when the range is amended.
This comment was minimized by the moderator on the site
Hello, Mike,
May be there is no other good method to update the result automatically when you change the cell font color, so, you need selec t the formula cell and hit Enter key or click the Automatic under the Formulas tab to get the updated result.
If you have any other good ways, please comment here.
Thank you very much!
This comment was minimized by the moderator on the site
I did everything as instructed by I get #VALUE! instead of a total. I changed the formula to subtotal one single cell and it worked but when the formula has a range of cells (in my case D3:D53, I get the #VALUE result.
This comment was minimized by the moderator on the site
I can't seem to get past this syntax error that I am getting. I'm not very knowledgeable when it comes to visual basic, so I'm hoping that it is just a small typing error (although I did cut and paste the code from above, rather than retype). The machine is running Windows 10/Office 2013. The error says "compile error / syntax error". The VB windows shows the line with the IF statement in the SumByColor module as highlighted. Please tell me what I'm doing wrong.
This comment was minimized by the moderator on the site
I followed the instructions for SUMbyColor (for font).Showed up perfectly on my screen (my work pc has Excel 2010 ) however, on my boss's screen it comes up as '#NAME?' (fyi, his pc has the current Excel version).Is there any way to resolve this?
This comment was minimized by the moderator on the site
[quote]I followed the instructions for SUMbyColor (for font).Showed up perfectly on my screen (my work pc has Excel 2010 ) however, on my boss's screen it comes up as '#NAME?' (fyi, his pc has the current Excel version).Is there any way to resolve this?By Liz[/quote] Hello,LIZ： After inserting the code, you should save the workbook as Excel Macro-Enabled Workbook file format. And then when you open this Excel file next time, you should click Enable Content from the security warning ribbon to activate the macro code. Please try this.
This comment was minimized by the moderator on the site
Hi tks for the advice. I have a similar problem of MYTAWALBEH I would like to include more columns in the function would be great if someone has some tips cheers
This comment was minimized by the moderator on the site
Great read and working brilliant however is it possible for anyone to explain how I could apply this to a multiple set of sheets?? Any idea will be much appreciated so cheers in advance.
This comment was minimized by the moderator on the site
Great read and works awesome but could someone please tell me how to apply this to multiple sheets. If anyone can, this will be brilliant for what I need and will be much appreciated indeed.
This comment was minimized by the moderator on the site
Is there any way to count the cells by colour without Kutools?
This comment was minimized by the moderator on the site
Hi! Works great, thanks. However, doesnt work for cells with more than one colour. Is it possible to update this code to do this? Nikki
This comment was minimized by the moderator on the site
How do I get the spread sheet to automatically update? If my data/color changes the sum doesn't update until I select the cell with the formula, hit F2, and then click off the cell. I want my sheet to automatically update as the data changes. Thanks.
This comment was minimized by the moderator on the site
Sir#name error aa rahi hai
This comment was minimized by the moderator on the site
Hi I need to count the number of cells in orange font from cell range B:B if Cell Range AB says 'Ventura' The above allows you to count according to one criteria but doesn't allow a second - I have tried but I have never written macros before and cannot get this working. Urgent help needed please? Thank you Leanne
This comment was minimized by the moderator on the site
I followed your directions for the count but in the cells that I have =CountColour(...) I have the #NAME error. I have multiple tabs, so I put the code in the sheet where I want to use it but I still got the error, so then I put it in a module. What am I missing??
This comment was minimized by the moderator on the site
Click the Microsoft Office Button , and then click Excel Options. Click Trust Center, click Trust Center Settings, and then click Macro Settings. Click the options to enable macro
This comment was minimized by the moderator on the site
thank you very much... needed a little debugging but I think that's because nothing is ever cookie cutter, what I did to fix my issues may be different than what anyone else may need to do (based on version, etc.) but once I took out the rem'd line and made sure that I was pointing to the right fields and format field to do the comparison to, it worked like a charm.
This comment was minimized by the moderator on the site
the countcolor function includes the spaces between the cell that has that specific color. can you fix this? thanks alot!
This comment was minimized by the moderator on the site
MYTawalbeh, I had the same issue. You have to save the workbook as a macro enabled workbook so the macro copies over. Otherwise you will continue to have the error because the macro isn't being copied over.
This comment was minimized by the moderator on the site
IT worked ,, thank you But when I save and close worksheet then re-open, it shows (#Name?) I've saved it (Macro-enabled)
This comment was minimized by the moderator on the site
Click to enable macros on the yellow bar at the top of the page when you open your document and your formulas will come back. Hope this helps.
This comment was minimized by the moderator on the site
Excellent work! Saved my wife's night! Thx
This comment was minimized by the moderator on the site
Hello, I keep getting an error as follows "Ambiguous name detected: SumByColor". What am i doing wrong?
This comment was minimized by the moderator on the site
I got this message when I tried to apply the same thing to another spreadsheet. When you go to insert module and copy in the text change it to SumByColour (different spelling and note there are two instances of it in the text). Then when I tried to use the formula in my second spreadsheet the new spelling came up which I selected and now the function works in this spreadsheet too. I hope this works for you because I am finding it extremely useful for various spreadsheets I work with.
This comment was minimized by the moderator on the site
I use a very similar method but have been playing with MULTI Colored fonts... say [Only the BOLD word is red], fingers crossed you have a thought as to how I may achieve this! Cheers Eddie
This comment was minimized by the moderator on the site
super cool, helped me a ton. if you don't mind me asking. when i try this on a colored cell number affected with "Conditional Formatting" it seems to see all the numbers as a single color. are there any way to have the program recognize that the color is different.
This comment was minimized by the moderator on the site
it works very good and so nice function. thank you.
This comment was minimized by the moderator on the site
How do we do well to access?
This comment was minimized by the moderator on the site
Hi, when i use this formula it keeps coming up with "Compile Error: Must be first statement on the line" and it has highlighted the "Function" on the first line of the formula. Help??
This comment was minimized by the moderator on the site
May be you did not insert a module instead copied the code on the screen after Alt+F11
This comment was minimized by the moderator on the site
Thank you so much! It works like wonder!
There are no comments posted here yet