Top 3 Methods to Count Colored Cells In Excel

There is no built-in function to count colored cells in Excel, but below mentioned are three different methods to do this task.

  • Count colored cells by using the Auto Filter optionCount colored cells by using the VBA codeCount colored cells by using the FIND method

Now, let us discuss each of them in detail –

For this example, let us look at the below data.

As we can see, each city is marked with different colors. So, we need to count the number of cities based on cell color.

As we can see, all the colors in the data. Now, we must choose the color that we want to filter.

Similarly, now we must choose other colors to get the count of the same.

  • We must first apply the filter to the data. At the bottom of the data, we need to apply the SUBTOTAL function in Excel to count cells. The SUBTOTAL function contains many formulas. It is helpful if we want to count, sum, and average only visible cell data. Under the heading “PIN,” we must click on the drop-down list filter and select “Choose by Color.” As we can see, all the colors in the data. Now, we must choose the color that we want to filter. Wow!!! As we can see in cell D21, our SUBTOTAL function is given the count of filtered cells as 6 instead of the previous result of 18.

Wow!!! As we can see in cell D21, our SUBTOTAL function is given the count of filtered cells as 6 instead of the previous result of 18.

So, blue-colored cells count to five now.

#2 – Excel Count Colored Cells by using VBA Code

VBA’s street smart techniques help us reduce time consumption at our workplace for some complicated issues.

We can reduce time, but we can also create our functions to fit our needs. For example, we can create a function to count cells based on color in one such function. Below is the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more to create a function to count cells based on color.

Code:

Then, copy and paste the above code to your module.

Function Color_Cell_Count(ColorCell As Range, DataRange As Range)

Dim Data_Range As Range Dim Cell_Color As Long

Cell_Color = ColorCell.Interior.ColorIndex

For Each Data_Range In DataRange

If Data_Range.Interior.ColorIndex = Cell_Color Then Color_Cell_Count = Color_Cell_Count + 1 End If

Next Data_Range

End Function

This code is not a SUB Procedure to run. Rather, it is a “User Defined FunctionUser Defined FunctionUser Defined Function in VBA is a group of customized commands created to give out a certain result. It is a flexibility given to a user to design functions similar to those already provided in Excel.read more” (UDF).

The first line of the code “Color_Cell_Count” is the function name. Now, we must create three cells and color them as below.

Now, we must open the function “Color_Cell_Count” in the G2 cell.

Even though we do not see the syntax of this function, the first argument is what color we need to count, so we must select cell F2.

The second argument is to select the range of cells as D2:D19.

Now, close the bracket and press the “Enter” key. As a result, it will provide the count of cells with the selected cell color.

Like this, with the help of UDF in VBA, we can count cells based on cell color.

#3 – Excel Count Colored Cells by Using FIND Method

We can also count cells based on the FIND method as well.

  • Step 1: First, we must select the range of cells where we need to count cells.

  • Step 2: Now, we need to press Ctrl + F to open the FIND dialog box.

  • Step 3: Now, click on “Options».”

  • Step 4: Consequently, it will expand the “Find” dialog box. Now, we must click on the “Format” option.

  • Step 5: Now, it will open up the “Find Format” dialog box. We need to click on the “Choose Format From Cell” option.

  • Step 6: Now, move the mouse pointer to see the pointer to select the format cell in excelFormat Cell In ExcelFormatting cells is an important technique to master because it makes any data presentable, crisp, and in the user’s preferred format. The formatting of the cell depends upon the nature of the data present.read more that we are looking to count.

  • Step 7: We will select the cell formatted as the desired cell count. We have chosen the F2 cell as the desired cell format, and now we can see the preview.

  • Step 8: Now, click on the “Find All” option to get the count of the selected cell format count of cells.

So, a total of 6 cells were found with selected formatting colors.

Things to Remember

  • The provided VBA code is not a Subprocedure in VBASubprocedure In VBASUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more; it is a UDF.The SUBTOTAL contains many formulas used to get the result only for visible cells when the filter is applied.We do not have any built-in function in Excel to count cells based on the color of the cell.

This article has been a guide to Count Colored Cells in Excel. We learned to count colored cells using the auto filter option, VBA code, FIND method, and downloadable Excel template. You may learn more about Excel from the following articles: –

  • Shade Alternate Rows in ExcelCountif not Blank in ExcelCount Rows in ExcelWord Count in Excel