Here is the pseudoriginaled content, maintaining the original structure, language, and image positions:

In this article, you will discover innovative methods to sum and count cells in Excel based on their color. These approaches work for cells colored manually or via conditional formatting across all versions of Excel from 2010 through Excel 365.

Despite Excel's wide array of functions for various tasks, none can compute cells based on their color. Apart from third-party tools, the most efficient solution is creating your own functions. Don’t worry if you’re unfamiliar with user-defined functions or have never encountered the term before. The functions are pre-written and tested by us. All you need to do is add them to your workbook!

Below, you'll find the codes for two custom functions (commonly referred to as user-defined functions or UDFs). The first function counts cells with a specific fill color, while the other counts cells based on font color. Both were crafted by Alex, one of our top Excel experts.

Custom functions to count by color in Excel Function CountCellsByColor(data_range As Range, cell_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = cell_color.Cells(1, 1).Interior.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Interior.Color Then cntRes = cntRes 1 End If Next cellCurrent CountCellsByColor = cntRes End Function Function CountCellsByFontColor(data_range As Range, font_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = font_color.Cells(1, 1).Font.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Font.Color Then cntRes = cntRes 1 End If Next cellCurrent CountCellsByFontColor = cntRes End Function Once the functions are added to your workbook, they will perform their calculations in the background, and you can use them just like any other native Excel function. From the end-user perspective, the functions appear as follows:

Count cells by fill color

To count cells with a specific background color, use this function:

CountCellsByColor(data_range, cell_color) Where:

• Data_range is the range in which to count cells.
• Cell_color is a reference to the cell with the target fill color.

To count cells of a specific color within a given range, follow these steps:

1. Add the CountCellsByColor function code to your workbook.
2. Begin typing the formula in the cell where you want the result to appear: =CountCellsByColor(
3. For the first argument, input the range where you wish to count colored cells.
4. For the second argument, provide the cell with the target color.
5. Press Enter. Done!

For example, to determine how many cells in range B3:F24 share the same color as H3, the formula is:

=CountCellsByColor(B3:F24, H3)

In our sample dataset, cells with values less than 150 are colored yellow, and those with values greater than 350 are green. The function easily calculates both counts:

Count cells by font color

If your cell values have different font colors, you can count them using this function:

CountCellsByFontColor(data_range, font_color) Where:

• Data_range is the range in which to count cells.
• Font_color is a reference to the cell with the sample font color.

For example, to find the number of cells in B3:F24 with the same font color as H3, the formula is:

=CountCellsByFontColor(B3:F24, H3)

Tip. If you prefer to rename the functions, feel free to modify the names directly in the code.

How to sum by color in Excel

To sum colored values, add the following two functions to your workbook. Similar to the previous example, the first handles fill color, and the other deals with font color.

Custom functions to sum by color in Excel Function SumCellsByColor(data_range As Range, cell_color As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = cell_color.Cells(1, 1).Interior.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Interior.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByColor = sumRes End Function Function SumCellsByFontColor(data_range As Range, font_color As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = font_color.Cells(1, 1).Font.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Font.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByFontColor = sumRes End Function ### Sum values by cell color

To sum by fill color in Excel, use this function:

SumCellsByColor(data_range, cell_color) Where:

• Data_range is the range in which to sum values.
• Cell_color is a reference to the cell with the fill color of interest.

For example, to add up the values of all cells in B3:F24 shaded with the same color as H3, the formula is:

=SumCellsByColor(B3:F24, H3)

Sum values by font color

To sum numeric values with a specific font color, use this function:

SumCellsByFontColor(data_range, font_color) Where:

• Data_range is the range in which to sum cells.
• Font_color is a reference to the cell with the target font color.

For instance, to add up all the values in cells B3:F24 with the same font color as the value in H3, the formula is:

=SumCellsByFontColor(B3:F24, H3)

Count and sum by color across entire workbook

To count and sum cells of a certain color in all sheets of a given workbook, we’ve created two separate functions named WbkCountByColor and WbkSumByColor. Here’s the code:

Custom functions to count and sum by color across workbook Function WbkCountByColor(cell_color As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes CountCellsByColor(wshCurrent.UsedRange, cell_color) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkCountByColor = vWbkRes End Function Function WbkSumByColor(cell_color As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes SumCellsByColor(wshCurrent.UsedRange, cell_color) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkSumByColor = vWbkRes End Function Note. To make the functions’ code more concise, we refer to the previously discussed functions that count and sum within a specified range. Thus, for the “workbook functions” to work, be sure to add the code of the CountCellsByColor and SumCellsByColor functions to your Excel too.

How to count colored cells in entire workbook

To find out how many cells of a particular color exist in all sheets of a given workbook, use this function:

WbkCountByColor(cell_color) The function takes just one argument—a reference to any cell filled with the color of interest. Therefore, a practical formula might look

The above is the detailed content of Excel: count and sum cells by color. For more information, please follow other related articles on the PHP Chinese website!