For the second argument, supply the cell with the target color.įor example, to find out how many cells in range B3:F24 have the same color as H3, the formula is:.

For the first argument, enter the range in which you want to count colored cells.

In a cell where you want the result to appear, start typing the formula: =CountCellsB圜olor(.

Insert the code of the CountCellsB圜olor function in your workbook.

To count cells of a specific color in a given range, carry out these steps:

Cell_color is a reference to the cell with the target fill color.

Data_range is a range in which to count cells.

To count cells with a particular background color, this is the function to use:ĬountCellsB圜olor(data_range, cell_color) From the end-user perspective, the functions have the following look. Once the functions are added to your workbook, they will do all work behind the scenes, and you can use them in the usual way, just like any other native Excel function. IndRefColor = font_color.Cells(1, 1).Font.Color If indRefColor = ThenĮnd Function Function CountCellsByFontColor(data_range As Range, font_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range IndRefColor = cell_color.Cells(1, 1).Interior.Color

Fastest way to calculate colored cells in Excelįunction CountCellsB圜olor(data_range As Range, cell_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range.

How to force custom functions to update.

Count and sum conditionally formatted cells.

Count and sum colored cells in entire workbook.

All you need to do is to insert them in your workbook :) The functions are already written and tested by us. If you know very little about user-defined functions or have never heard of this term before, don't panic.

Aside from third-party tools, there is only one efficient solution - create your own functions. These solutions work for cells colored manually and with conditional formatting in all versions of Excel 2010 through Excel 365.Įven though Microsoft Excel has a variety of functions for different purposes, none can calculate cells based on their color. In this article, you will learn new effective approaches to summing and counting cells in Excel by color.

Tally text light 1 free font download