Excel COUNTIF Function with Conditional Formatting (7 Examples)

Looking for ways to use the COUNTIF function with conditional formatting in Excel? Then, this is the right place for you. We can highlight cells depending on different criteria using conditional formatting. For this purpose, you may use different formulas to select specific cells. Here, you will find 7 examples of using the COUNTIF function in those formulas to format cells in Excel.


Download Practice Workbook

You can download the workbook to practice yourself.


7 Examples of Using COUNTIF Function with Conditional Formatting in Excel

Here, we have a dataset containing the name of some Sales Person, Product and their Sales values. Now, we will show how you can use the COUNTIF function with conditional formatting using this dataset in Excel.

Excel COUNTIF Conditional Formatting


1. Highlight Duplicate Values Using COUNTIF Function with Conditional Formatting

Sometimes, you may have duplicate values in your dataset that you want to highlight. You can highlight these duplicate values using the COUNTIF function with conditional formatting in Excel.

Here are the steps.

Steps:

  • Firstly, select cell range B5:D11.
  • Then, go to the Home tab >> click on Conditional Formatting >> select New Rule.

Highlight Duplicate Values Using COUNTIF Function with Conditional Formatting in Excel

  • Now, the New Formatting Rule box will appear.
  • Next, select Use a formula to determine which cells to format.
  • After that, insert the following formula in the box.
=COUNTIF($B$5:$D$11,B5)>1
  • Then, click on Format.

Opening New Formatting Rule Box to use COUNTIF Formula in Conditional Formatting in Excel

Here, in the formula, we inserted cell range B5:D11 as range and Cell B5 as criteria to find duplicate values.
  • Now, the Format Cells box will open. Using this box, you can format cells according to your wish.
  • Here, we will go to the Fill option.
  • Then, select Blue, Accent 5, Lighter 80% as Background Color.
  • After that, click on OK.

Opening New Format Cells Box to use COUNTIF Formula in Conditional Formatting in Excel

  • Again, the New Formatting Rule box will open.
  • Lastly, click on OK.

  • Finally, you will see that the duplicate cells have been highlighted.

Read More: How to Use COUNTIF for Non Contiguous Range in Excel


2. COUNTIF Formula to Format Cells Equal to Specific Cell Range

Suppose, you have a dataset containing some Ticket No from 5 different Ticket Counters and also the Winning Ticket numbers in a different cell range. Now, if you want you can highlight the cells containing the winning ticket numbers in the dataset using the COUNTIF formula in conditional formatting.

Follow the steps given below to do that.

Steps:

  • To start with, select cell range C5:E9.
  • After that, go to the Home tab >> click on Conditional Formatting.

COUNTIF Formula to Format Cells Equal to Specific Cell Range in Excel

  • Then, select New Rules.

  • Now, the New Formatting Rule box will appear.
  • Next, select Use a formula to determine which cells to format.
  • Afterward, insert the following formula in the box.
=COUNTIF($C$11:$E$11,C5)>=1
  • Then, click on Format.

In the above formula, we checked if the values of cell range C11:E11 are equal in the selected cell range for formatting, then it will format that specific cell.
  • After that, format the cells going through the same steps shown in Example 1.
  • Thus, you will be able to format cells equal to a specific cell range by inserting the COUNTIF formula in conditional formatting.

Read More: COUNTIF Function to Count Cells That Are Not Equal to Zero


3. Highlight Items from List Using Conditional Formatting in Excel

Next, we will show you how you can highlight items from a list or use a named range using conditional formatting in Excel.

Here, we have a dataset containing the names of some Sales Person and Product. Now, suppose you want to highlight the cells in the dataset that represents the Selected Product List.

Here are the steps which you need to follow to do this.

Steps:

  • Firstly, select cell range E5:E7.
  • Then, go to the Name box and type “Product”.
  • Next, press Enter.

Using Name Box to Apply Excel COUNTIF Formula in Conditional Formatting

  • Now, select cell range C5:C10.
  • After that, go to the Home tab >> click on Conditional Formatting >> select New Rules.

  • Then, insert the following formula in the New Formatting Rule box.
=COUNTIF(Product,C5)
  • Afterward, click on Format.

Highlight Items from List Using COUNTIF Formula in Conditional Formatting in Excel

Here, in the formula, we search for the values of named range Product in the selected cell range to format those cells.
  • Lastly, format cells going through the same steps shown in Example 1.
  • Thus, you can highlight items from a list using conditional formatting in Excel.

Read More: How to Use COUNTIF Function to Count Text from List in Excel


4. Excel COUNTIF Formula for Text Values in Conditional Formatting

In the fourth example, we will show you how you can format cells that contain a specific text value by applying the COUNTIF formula in conditional formatting.

Follow the steps given below to do that.

Steps:

  • In the beginning, select cell range C5:C10.
  • Next, go to the Home tab >> click on Conditional Formatting >> select New Rule.

  • Then, choose Use a formula to determine which cells to format as Rule type.
  • Now, type the following formula in the box.
=COUNTIF(C5,"Pen")
  • Lastly, click on Format.

Excel COUNTIF Formula for Text Values in Conditional Formatting

In the above formula, we formatted the cells which contain the text value “Pen” in the selected cell range.
  • After that, format the cells according to your choice as shown in Example 1.
  • Finally, you will get the cells that contain that “Pen” as text value by applying the COUNTIF function.

Excel COUNTIF Conditional Formatting

Read More: Excel COUNTIF to Count Cell That Contains Text from Another Cell


Similar Readings


5. Count Cells Greater Than or Less Than Using Conditional Formatting

If you want to highlight cells that contain greater than or less than a particular value, you can also do that using the COUNTIF formula in conditional formatting in Excel.

Here, we will show you how you can highlight cells that contain values greater than $100 and less than $80. Go through the steps given below to do that on your own.

Steps:

  • Firstly, select cell range D5:D10.
  • After that, go to the Home tab >> click on Conditional Formatting.

  • Then, select the New Rule.

  • Now, in the New Formatting Rule box insert the following formula.
=COUNTIF(D5,"<80")
  • Next, click on Format and format cells similar to the way shown in Example 1.

Count Cells Less Than Using Excel COUNTIF Formula in Conditional Formatting

The above formula in the box is used to format cells containing values that are less than $80.
  • Thus, the cells that contain values less than $80 will be highlighted.

  • Again, open the New Formatting Rule box by going through the steps shown above and insert the following formula.
=COUNTIF(D5,">100")
  • Similarly, click on Format.

Count Cells Greater Than Using COUNTIF Formula Conditional Formatting in Excel

In the above formula, we formatted cells containing values that are greater than $100.
  • Now, the Format Cells box will appear.
  • Then, go to the Fill option >> select Orrange, Accent 2, Lighter 80%.
  • Lastly, click on OK.

  • Again, press OK.

  • Finally, the cells that contain values greater than $100 will be highlighted.

Read More: Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)


6. Apply COUNTIF Function with Multiple Criteria for Conditional Formatting

Now, we will show you how you can apply the COUNTIF function with multiple criteria for conditional formatting in Excel.

Here, we will highlight the corresponding Sales Person’s name who had a Sales value less than $80 or greater than $120.

Steps:

  • To start with, open the New Formatting Rule box going through the same steps shown in Example 1.
  • Then, type the following formula in the box.
=COUNTIF(D5,"<80")-COUNTIF(D5,">120")
  • Next, click on OK.

Apply Excel COUNTIF Function with Multiple Criteria for Conditional Formatting

Here, in the formula, we set Sales value less than $80 and Greater than $120 as multiple criteria.
  • Similarly, format the cells as shown in Example 1.
  • Thus, you can use the COUNTIF function with multiple criteria for conditional formatting in Excel.

Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria


7. Format Cells Using Excel COUNTIF Function with OR Criteria

In the last example, you will find a way to format cells using the COUNTIF function with OR criteria in Excel.

Here, we will highlight the corresponding Sales Person’s name who sold Pencil and Staplers using conditional formatting.

Steps:

  • Firstly, open the New Formatting Rule box going through the same steps shown in Example 1.
  • Next, type the following formula in the box.
=COUNTIF(C5,"Pencil")+COUNTIF(C5,"Staplers")
  • After that, click on OK.

Format Cells Using Excel COUNTIF Function with OR Criteria

The above formula can find out cells that contain “Pencil” or “Staplers” as text in the selected cell range. Here, to perform OR criteria we inserted “+” between the two COUNTIF functions.
  • Lastly, format cells going through the same steps shown in Example 1.
  • Thus, you can format cells using the COUNTIF functions with OR criteria.

Read More: COUNTIF Excel Example (22 Examples)


Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Practice Section


Conclusion

So, in this article, we have shown you 7 examples of using the COUNTIF function with conditional formatting in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin Islam

Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo