## 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 color with an amazing feature-Count by Color

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

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Enhanced with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or 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  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ... 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 Toolsets:  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, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

#### 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!