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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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

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

  • Now, select Cell E13 and type the formula:
=SUBTOTAL(102,6:6)

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

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’.

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

  • Use the formula below for row 8:
=SUBTOTAL(102,8:8)

And the formula below for row 11:

=SUBTOTAL(102,11:11)

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

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.

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

  • Finally, you will get results like below.

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

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.

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

  • After that, a New Name window will appear.

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

  • Now, type the name of the function in the Name field.
  • Then, type the formula in the ‘Refers to’ field.
=GET.CELL(38,'COUNTIF Function'!C6)

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

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:
=ColorCode

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

  • Hit Enter to see the result.

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

  • Here select Cell F13 and type the formula:
=COUNTIF(G6:J6,36)

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:
=COUNTIF(G8:J8,40)

And use the formula below for row 11:

=COUNTIF(G11:J11,37)

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo