Excel Formula to Count Colored Cells in a Row (2 Effective Methods)

In this article, we will learn to count colored cells in a row with an Excel formula. Sometimes, we need to use colors to highlight certain cells in specific rows. Unfortunately, there are no built-in functions in excel to count colored cells in a row. But we can easily count colored cells using the excel formula. Go through the following methods to know more about counting colored cells in a row.

How to Count Colored Cells in a Row with Formula in Excel: 2 Effective Ways

To explain these methods, we will use a dataset that contains the sales amount of the first five months of some sellers. Here, we have filled the cells manually with different colors. We can see there are colored cells in row 6, row 8 and row 11. We will try to count colored cells with an excel formula.

1. Count Colored Cells in a Row Using Excel Formula with  SUBTOTAL Function

In this method, we will use the SUBTOTAL Function to count colored cells. The SUBTOTAL Function returns a subtotal in a list or database. We will have to apply filters in our dataset to apply this method.

Let’s follow the steps to learn this method.

STEPS:

• In the first place, select any cell in your dataset. We have selected Cell C5 here.

• Secondly, go to the Data tab and select Filter.

• After applying Filter, your dataset will look like below.

• Now, select Cell E13 and type the formula:

Here, inside the SUBTOTAL Function, we have inserted the function_num in the first argument and inserted row 6 in the second argument. function_num 102 counts by ignoring hidden rows.

• Press Enter to see the result. It will return ‘5’.

• Use the formula below for row 8:

And the formula below for row 11:

Here, the formulas return the number of colored cells in the rows.

• Again, to use the filter by color, select a header and select Filter by Color to choose a color.

• Finally, you will get results like below.

Here. we have filtered by the light green color. So, row 8 and row 11 are displaying 0.

Read More: How to Count Colored Cells in Excel with VBA

2. Use Formula with COUNTIF & GET.CELL Functions to Count Colored Cells in a Row in Excel

We can also use the GET.CELL and COUNTIF Functions simultaneously to count colored cells in a row. We can apply the GET.CELL Function from the Name Manager. We will use the previous dataset here. But we will delete the May column to make the explanation easier.

Let’s pay attention to the steps below to learn this method.

STEPS:

• In the beginning, go to Formulas and select Define Name from the Name Manager.

• After that, a New Name window will appear.

• Now, type the name of the function in the Name field.
• Then, type the formula in the ‘Refers to’ field.

Here, the first argument of the GET.CELL Function returns the color code as a number. The second argument indicates the sheet name and the reference cell number.

• Click OK to proceed.
• Next, go to Cell G6 and type the formula:

• Hit Enter to see the result.

• Copy the formula to G6:J11 using the Fill Handle.

• Here select Cell F13 and type the formula:

Here, the COUNTIF Function counts the value 36 in the range G6:J6. 36 is the color code of the light green color.

• Press Enter to see the result. It will return ‘4’.

• Finally, use the formula below for row 8:

And use the formula below for row 11:

Read More: How to Count Colored Cells In Excel Without VBA

Things To Remember

There are some things you need to remember when you want to count colored cells in a row.

• You can use the Filter and SUBTOTAL Function method even if conditional formatting is applied in your dataset.
• You need to save our workbook as a macro-enabled file when we are using the CELL Function. Otherwise, it will not work.

Download Practice Book

Download the practice book here.

Conclusion

We have discussed two easy and quick methods to count colored cells in a row in this article. I hope these methods will help you to count colored cells. You can also use these methods to count colored cells in a column or in a specific range by following the steps easily. We have also added the practice book at the beginning of the article. Download and exercise the practice book to learn these methods. Last of all, if you have any suggestions or queries, feel free to ask in the comment section.

Related Articles

• Count Cells by Color with Conditional Formatting in Excel
  
• Excel Formula to Count Cells with Specific Color
• How to Count Blank Colored Cells in Excel
• How to Count Cells with Specific Text and Fill/Font Color in Excel