To count colored cells in Excel, open the work sheet with your data and run Sum and Count by All Colors: The add-in selects the entire data range in your worksheet automatically. You can change the range by entering the new address manually or selecting it in you sheet. You can get the entire table selected automatically.
There is no built-in function to count colored cells in excel, but below mentioned are three different methods to do this task. For this example, 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. Follow the below steps to count cells by color.
How to count colored cells in Excel without any VBA code?
The COUNTIF function used Get, bg Cells named range. The named range refers to adjacent cell in column A containing the background color. $E$2:$E$15 refers to the column containing the excel color code of all cells. Till now we just saw how to count colored cells in excel without using any VBA code.
One click to count, sum, and average colored cells in Excel. With the excellent Count by Color feature of Kutools for Excel, you can quickly count, sum, and average cells by specified fill color or font color with only one click in Excel. Besides, this feature will also find out the max and min values of cells by the fill color or font color.
How to Count Colored or Highlighted Cells in Excel The COUNT function in Excel counts cells containing numbers in Excel. You cannot count colored or highlighted cells with the COUNT function. But you can follow a few workarounds to count colored cells in Excel.
Which function can be used to count the colored cells?
We all are well aware of the fact that COUNT and COUNTIF functions are the ones that can be used to count the cells with numbers and cells that follow any specific criteria. Isn’t it being fancy to have a function which counts the colored cells?
How many cells have the same color as filtered in Excel?
You can see there are 419 cells which have the same color as filtered, i., and e., and light orange. You similarly can check the count of other colors as well. This works fine because of the use of the COUNT function (102) within the SUBTOTAL as an argument.
What is the use of%countcolor in Excel?
COUNTCOLOR counts all cells that have the appropriate font or background color and it doesn’t matter what is in the cells: text, numbers, or nothing: Use the AVERAGECOLOR function to get the average of all cells that have this background or font color, empty as well.