Go Back

Using the COUNTIF Function of Excel’s Conditional Formatting

The usage of the COUNTIF & Conditional Formatting functions in Excel can yield useful and visually appealing results. This step by step tutorial will assist all levels of Excel users in creating dynamic conditional formats.

Our data-set shows the date and the type of vehicle rented. Now, we want to show the overall count of a type of vehicle with color coding.

Figure 1: Dataset containing Date and Vehicle Type

Usage of the “COUNTIF” Excel Function

Syntax of the COUNTIF Formula: COUNTIF(range, criteria)

We are going to apply the COUNTIF function to count the number of vehicles sold by type in the first 11 days of November. We have three types of vehicles – “Car”, “Motorcycle” and “Bicycle”.

Figure 2: Usage of the COUNTIF Formula on the given dataset

  • We will calculate the number of vehicles within each type in cells G3 to G5, using the “COUNTIF” formula
  • We will break down the COUNTIF formula in cell G3 as below:
    = COUNTIF($C$3:$C$19,F3)
  • The range C3:C19 refers to our dataset that elaborates the type of vehicle rented out on a given date
  • F3 refers to the vehicle type that we would like to count, “Car” in this case

We can apply the same formula for the “Bicycle” and “Motorcycle” categories in cells G4 and G5 respectively. The results can be seen in the figure below.

Figure 3: COUNTIF result

Implementation of Conditional Formatting

Now, we will apply conditional formatting to the cells that contain the COUNTIF formula.

Conditional formatting helps us identify the least and most rented vehicle.

The application of the conditional formatting is discussed in the steps below:

  • Select cells G3:G5

Figure 4: Cell Selection for Conditional Formatting

  • Go to Home > Conditional Formatting and click the small drop down menu adjacent to conditional formatting

Figure 5: Selection of Conditional Formatting from Home

  • Select the preferred Color Scale

Figure 6: Selection of Color Scale

  • Select any option in Color Scale and the cells containing the COUNTIF formula will be highlighted based on the selected option.

Figure 7: Application of Conditional Formatting

Combining Excel functions together can help us solve complex tasks quickly.

The final result will be:

Figure 8: Final Result

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need to link the value of a drop down option to a COUNTIF function between tabs. Also, I need to use a conditional formatting function to give a pass/fail grade depending on the numbers generated from the previous functions. This is all between sheets in the same workbook.
Solved by C. J. in 27 mins
I need help with consistent formatting from someone who is fast and knows excel's shortcuts
Solved by S. D. in 27 mins
conditional formatting issue using the shading function with a formula
Solved by D. C. in 15 mins

Leave a Comment