I am trying to create countif but could not come right. Can anyone help me with the below formula I tried to create:


Where have I gone wrong?

Actuall I wanted Excel to count the number of cell that contain more that 50 and mark them as green, those that are 30 and above to mark them as amber and the ones below 30 to mark them as red.



Selected Answer

Separate countifs for each condition and use conditional formatting to highlight the cells according to your rules.



Can you please show me an example of what you imply.
ManZaakes (rep: 5) Jul 18, '17 at 5:05 am
Add to Discussion

Hi ManZaakes!

You need two separate types of formulas, one to count and one to change the color using conditional formatting.

To count cells that are lower than 30:


To count cells that are higher than 30 but lower than 50:


To count cells that are higher than 50:


A1:A5 is the test range for the numbers.

To change the color of the cells, you need to use a custom formula for conditional formatting. 

Just use IF statements to check the number range and return true or false.


You would need to apply all three formulas to conditional formatting onto the cell that should change color and simply assign a different color for each formula. This means 3 conditional formatting rules per cell.

You can quickly apply the rule to multiple cells using copy/paste or in the window where you manage conditional formatting rules.

Hope this helps!


Answer the Question

You must create an account to use the forum. Create an Account or Login