Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

doc-count-font-color1

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.In Excel, there is no direct formula 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 Kutools for Excel

Count or sum cells based on background color with Kutools for Excel


Count / Sum cell values based on background, font, conditional formatting color:

Kutools for Excel's Count by Color feature can help you to get some calculations based on font, background or conditional formatting color as you need.

doc filter color 12

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble 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 20140210
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:D8,A1) , see screenshot:

doc-count-font-color1

Note: In the above formula, A1:D8 is the range that you want to use and A1 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:

doc-count-font-color1

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

doc-count-font-color1

Note: In the above formula, A1:D8 is the range that you want to use and A1 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:

doc-count-font-color1


arrow blue right bubble Count or sum cells based on font color with Kutools for Excel

May be the User Defined Function is troublesome for you to save and apply, here, I will recommend you a handy too- Kutools for Excel, with its Count by Color 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.

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

After installing Kutools for Excel, please do as follows:

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

2. Click Enterprise > Count by Color, see screenshot:

doc-count-font-color1

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, see screenshot:

doc-count-font-color1

4. And the cells with same font colors have been counted, added up, averaged and so on. And then click Generate report button, you will get statistics in a new workbook. See screenshot:

doc-count-font-color1

Download and free trial Kutools for Excel Now !


arrow blue right bubble Count or sum cells based on background color with Kutools for Excel

If you want to count or sum the cells with specific background color, Kutools for Excel’s Count by Color feature also can help you, please do as follows:

1. Select the data range that you want to count or sum the cells based on background color, and then enable this feature by clicking Enterprise > Count by Color.

2. In the Count by Color dialog box, choose Standard formatting from the Color method drop down, and select Background under the Count type drop down, see screenshot:

doc-count-font-color1

3. Then click Generate report button, a report is generated based on the background colors, see screenshot:

doc-count-font-color1

Click to know more about this Count by Color feature.

Download and free trial Kutools for Excel Now !


arrow blue right bubble Count or sum cells based on font / background / conditional formatting color:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Borja · 2 days ago
    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
  • To post as a guest, your comment is unpublished.
    Xiahui Tan · 2 months ago
    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
    • To post as a guest, your comment is unpublished.
      skyyang · 2 months ago
      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:
  • To post as a guest, your comment is unpublished.
    Kriss · 2 months ago
    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?
    • To post as a guest, your comment is unpublished.
      skyyang · 2 months ago
      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
  • To post as a guest, your comment is unpublished.
    Tahj · 3 months ago
    Exactly what I was looking for! This will save me lots of time. Thank You
  • To post as a guest, your comment is unpublished.
    Fernanda · 4 months ago
    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 - webchio@gmail.com