## Excel Tips: Count/sum cells by color (background, font, conditional formatting)

In daily tasks, color marking is a popular method for quickly distinguishing and highlighting crucial data. But, how do we count or sum cell data based on specific color (fill color, font color, conditional formatting)? By default, Excel doesn't offer a direct feature to count or sum by color. Nevertheless, with some tricks and indirect methods, we can still achieve this. This article will explore how to count or sum data by color.

Count and sum cells based on background color

Count and sum cells based on font color

Count and sum cells based on conditional formatting color

#### Count and sum cells based on background color

For example, if you have a range of data where the values are filled with different background colors as shown in the screenshot below. To count or sum the cells based on a specific color, Excel doesn't offer a direct feature to count or sum cells based on their background color. However, with a bit of ingenuity and some handy techniques, you can accomplish this task. Let's explore some useful methods in this section.

#### Count and sum cells by background color with User Defined Function

Here, we will show you how to create and use such a User Defined Function to solve this task in Excel. Please do with the following steps:

Step 1: Open the VBA module editor and copy the code

1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the opened window, click Insert > Module to create a new blank module.
3. Then, copy and paste the below code into the blank module.
VBA code: Count and sum cells based on background color
``````Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
'Updateby Extendoffice
Dim rCell As Range
Dim lCol As Long
Dim vResult As Double
lCol = rColor.Interior.ColorIndex
vResult = 0
If SUM Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = vResult + rCell.Value
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = vResult + 1
End If
Next rCell
End If
ColorFunction = vResult
End Function
``````

Step 2: Create formulas to count and sum cells by background color

After pasting the above code, close the module window, then apply the following formulas:

• Count cells based on the specific background color:
Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
``=colorfunction(G2,\$B\$2:\$E\$12,FALSE) ``
Note: In this formula, G2 is the reference cell with the specific background color you want to match; \$B\$2:\$E\$12 is the range where you want to count the number of cells of G2's color; FALSE is used to count cells with matching color.
• Sum cells based on the specific background color:
Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
``=colorfunction(G2,\$B\$2:\$E\$12,TRUE)  ``
Note: In this formula, G2 is the reference cell with the specific background color you want to match; \$B\$2:\$E\$12 is the range where you want to count the number of cells of G2's color; TRUE is used to sum cells with matching color.

#### Count and sum cells by background color with a powerful feature

For those unfamiliar with programming, VBA can appear quite complex. Here, we will introduce a powerful tool - Kutool for Excel, its Count by Color feature allows you to easily calculate (count, sum, average, etc.) based on background color in just a few clicks. Impressively, Count by Color feature goes beyond merely background colors – it can also differentiate and calculate based on font colors and conditional formatting.

After downloading and installing Kutools for Excel, first, select the data range that you want to count or sum cells based on a specific background color. Next, navigate to Kutools Plus and choose Count by Color.

In the Count by Color dialog box, please specify the operations:

1. Select Standard formatting from the Color method drop down list;
2. Specify Background from the Count type drop down list, and you can preview the statistical results for each background color in the dialogue box;
3. At last, click Generate report to export the calculated results to a new workbook.

Result:

Now you will get a new workbook with the statistics. See screenshot:

Tips:
1. The Count by Color feature also supports counting and summing cells based on standard font color, background or font color from conditional formatting, and a combination of both fill and conditional formatting colors.
2. Interested in this feature, please click to download to get a free trial for 30 days.

#### Count and sum cells by background color with Filter and SUBTOTAL function

Supposing we have a fruit sales table as below screenshot shown, and we will count or sum the colored cells in the Amount column.

Step 1: Apply the SUBTOTAL function

Select blank cells to enter the SUBTOTAL function.

• To count all cells with the same background color, please enter the formula:
``=SUBTOTAL(102, F2:F16)``
• To sum all cells with the same background color, please enter the formula;
``=SUBTOTAL(109, F2:F16)``
• Note: in the above formulas, 102 represents to count numeric values in a filtered list while excluding hidden cells; 109 represents to sum up values in a filtered list excluding hidden cells; F2:F16 is the range over which either the count or sum will be calculated.

Step 2: Filter cells based on specific color

1. Select the header of the table, and click Data > Filter. See screenshot:
2. Click the Filter icon  in the header cell of the Amount column, and click Filter by Color and the specified color you will count by successively. See screenshot:

Result:

After filtering, the SUBTOTAL formulas automatically count and sum the colored cells in the Amount column. See screenshot:

Note: This method requires the colored cells you will count or sum are in the same column.

#### Count and sum cells based on font color

Want to count or sum cells based on their font color in Excel? Let's say you have the data, like in the given screenshot, with cells containing texts in red, blue, orange and black color. Excel doesn't make this easy by default. But don't worry! In this section, we'll show you some simple tricks to do just that.

#### Count and sum cells based on font color with User Defined Function

To count and sum cells with specific font colors, the following User Defined Function may help you to solve this task. Please do with the following steps:

Step 1: Open the VBA module editor and copy the code

1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the opened window, click Insert > Module to create a new blank module.
3. Then, copy and paste the below code into the blank module.
VBA code: Count and sum cells based on font color
``````Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
'Updateby Extendoffice
Application.Volatile
Dim rng As Range
Dim xTotal As Double
Dim xCount As Double
xTotal = 0
xCount = 0
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
If UCase(FunctionType) = "SUM" Then
xTotal = xTotal + rng.Value
ElseIf UCase(FunctionType) = "COUNT" Then
xCount = xCount + 1
End If
End If
Next
If UCase(FunctionType) = "SUM" Then
ProcessByFontColor = xTotal
ElseIf UCase(FunctionType) = "COUNT" Then
ProcessByFontColor = xCount
Else
ProcessByFontColor = CVErr(xlErrValue)
End If
End Function
``````

Step 2: Create formulas to count and sum cells by font color

After pasting the above code, close the module window, then apply the following formulas:

• Count cells based on the specific font color:
Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
``=ProcessByFontColor(\$B\$2:\$E\$12,G2, "COUNT")``
Note: In this formula, G2 is the reference cell with the specific font color you want to match; \$B\$2:\$E\$12 is the range where you want to count the number of cells of G2's color.
• Sum cells based on the specific font color:
Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
``=ProcessByFontColor(\$B\$2:\$E\$12,G2, "SUM")  ``
Note: In this formula, G2 is the reference cell with the specific font color you want to match; \$B\$2:\$E\$12 is the range where you want to count the number of cells of G2's color.

#### Count and sum cells based on font color with an easy feature

Looking to effortlessly count or sum cell values in Excel based on font color? Dive into Kutools for Excel's Count by Color feature! With this smart tool, counting and summing cells by specific font color becomes a breeze. Discover how Kutools can transform your Excel experience.

After downloading and installing Kutools for Excel, first, select the data range that you want to count or sum cells based on a specific font color. Then, click Kutools Plus > Count by Color to open the Count by Color dialog box.

In the Count by Color dialog box, please specify the operations:

1. Select Standard formatting from the Color method drop down list;
2. Specify Font from the Count type drop down list, and you can preview the statistical results for each font color in the dialogue box;
3. At last, click Generate report to export the calculated results to a new workbook.

Result:

Now, you have a new workbook displaying the detailed statistics based on font color. See screenshot:

Tips: Interested in this feature, please click to download to get a free trial for 30 days.

#### Count and sum cells based on conditional formatting color

In Excel, you may commonly use the Conditional Formatting to apply specific color to cells that meet certain criteria, making data visualization intuitive. But what if you need to count or sum those specially formatted cells? While Excel doesn't offer a direct way for this, here are ways to maneuver around this limitation.

#### Count and sum conditionally formatted cells with VBA code

Counting and summing conditionally formatted cells in Excel is not straightforward using built-in functions. However, you can accomplish this task using VBA code. Let's go over how you can use VBA for this:

Step 1: Open the VBA module editor and copy the code

1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the opened window, click Insert > Module to create a new blank module.
3. Then, copy and paste the below code into the blank module.
VBA code: Count and sum cells based on conditional formatting color
``````Sub SumCountByConditionalFormat()
'Updateby Extendoffice
Dim sampleColor As Range
Dim selectedRange As Range
Dim cell As Range
Dim countByColor As Long
Dim sumByColor As Double
Dim refColor As Long
Set selectedRange = Application.InputBox("Select a range to evaluate:", _
"Kutools for Excel", _
Type:=8)
If selectedRange Is Nothing Then Exit Sub
Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
"Kutools for Excel", _
Type:=8)
If Not sampleColor Is Nothing Then
refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
For Each cell In selectedRange
If cell.DisplayFormat.Interior.color = refColor Then
countByColor = countByColor + 1
sumByColor = sumByColor + cell.Value
End If
Next cell
MsgBox "Count: " & countByColor & vbCrLf & _
"Sum: " & sumByColor, _
vbInformation, "Results based on Conditional Format Color"
End If
End Sub
``````

Step 2: Execute this VBA code

1. After pasting the code, press F5 key to run this code, a prompt box will appear, please select the data range where you want to count and sum cells based on conditional formatting. Then, click OK, See screenshot:
2. In another prompt box, select a specific conditional formatting color that you want to count and sum, and click OK button, see screenshot:

Result:

Now, the result, which includes both the count and sum of cells with the specified conditional formatting color, will be displayed in the popped-out box. See screenshot:

#### Count and sum conditionally formatted cells with a smart feature

If you're looking for other quick and easy methods to count and sum conditionally formatted cells, Kutools for Excel is your go-to solution. Its Count by Color feature can solve this task in just a few clicks. Dive in to discover the efficiency and precision Kutools can bring to your workflow.

After downloading and installing Kutools for Excel, first, select the data range that you want to count or sum cells based on a specific conditional formatting color. Then, click Kutools Plus > Count by Color to open the Count by Color dialog box.

In the Count by Color dialog box, please specify the operations:

1. Select Conditional formatting from the Color method drop down list;
2. Specify Background from the Count type drop down list, and you can preview the statistical results for each conditionla formatting color in the dialogue box;
3. At last, click Generate report to export the calculated results to a new workbook.

Result:

Now, you have a new workbook displaying the detailed statistics based on the conditional formatting color. See screenshot:

Tips: Interested in this feature, please click to download to get a free trial for 30 days.

#### Related Articles:

• If the font color is red then return a specific text
• How could you return a specific text if the font color is red in another cell as below screenshot shown? In this article, I will introduce some tricks for doing some operations based on the red font text in Excel.
• Filter data by multiple colors
• Normally, in Excel, you can quickly filter rows with only one color, but, have you ever considered filtering rows with multiple colors at the same time? This article, I will talk about quick trick for you to deal with this problem.
• Add color to drop down list
• In Excel, create a drop-down list can help you a lot, and sometimes, you need to color coded the drop down list values depending on the corresponding selected. For instance, I have created a drop-down list of the fruit names, when I select Apple, I need the cell is colored with red automatically, and when I choose Orange, the cell can be colored with orange.
• Color alternate rows for merged cells
• It is very helpful to format alternate rows with a different color in a large data for us to scan the data, but, sometimes, there may be some merged cells in your data. To highlight the rows alternately with a different color for the merged cells as below screenshot shown, how could you solve this problem in Excel?
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
This comment was minimized by the moderator on the site
I copied and paste but calculation result is "0" why?? I am using Office 2016.
This comment was minimized by the moderator on the site
I try the same you this command =COUNTBYCELLCOLOR is counting only fill color but is not count by condition formating. Please help to improve code thx.
This comment was minimized by the moderator on the site
I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
This comment was minimized by the moderator on the site
#NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
This comment was minimized by the moderator on the site
I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
This comment was minimized by the moderator on the site
works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
This comment was minimized by the moderator on the site
Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
There are no comments posted here yet