# Excel COUNTIF Function with Conditional Formatting (7 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Here, we have a dataset containing the names 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. ## 1. Highlighting 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. • 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. Here, in the formula, we inserted cell range B5:D11 as the range and Cell B5 as the 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. • Again, the New Formatting Rule box will open.
• Lastly, click on OK. • Finally, you will see that the duplicate cells have been highlighted. ## 2. Applying 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:

• After that, go to the Home tab >> click on Conditional Formatting. • 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 Between Two Cell Values in Excel

## 3. Highlighting 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 represent the Selected Product List. Here are the steps that 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. • 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. 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. ## 4. Utilizing 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. In the above formula, we formatted the cells that 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 a text value by applying the COUNTIF function. ## 5. Counting 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. 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. 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. ## 6. Applying the 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. Here, in the formula, we set the 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. ## 7. Formatting 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. The above formula can find 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. ## Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own. ## 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. 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 Advanced Excel Exercises with Solutions PDF  