200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I am trying to create countif but could not come right. Can anyone help me with the below formula I tried to create:
=COUNTIF(I14:I27>=50"GREEN";=>30"AMBER";<30"RED")
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.
Separate countifs for each condition and use conditional formatting to highlight the cells according to your rules.
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:
=COUNTIFS(A1:A5,"<30")To count cells that are higher than 30 but lower than 50:
=COUNTIFS(A1:A5,">30",A1:A5,"<50")To count cells that are higher than 50:
=COUNTIFS(A1:A5,">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.
=IF(A1<30,TRUE,FALSE)=IF(AND(A1>30,A1<50),TRUE,FALSE)=IF(A1>50,TRUE,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!