# Using Countif and if to count text then format result compared to another cell

✭✭✭✭

Hi I have a countif formula =COUNTIF(Wed10:Wed51, "HPT" to count the number of times HPT shows in a column. I want to format the result by color compared to another cell. For example: i need 5 however; the countif returns 4(counts 4 HPT in the column) I want the 4 to show red, if it's equal = black, if it's higher than green. How(if possible) can I apply that formula to the cell with the countif already there? is it possible?

• ✭✭✭✭✭✭

The conditional formatting tool can do this. You just need to build 1 criteria for each option.

• ✭✭✭✭

Actually it doesn't work in this case because i need row formatting, not column formatting. There are other cells in the column that i don't want the formatting to apply to just specific cells. using conditional formatting works, but it applies to other cells which i don't want it to. basically i have a difference cell that if it's a negative number i want it red, if it's a positive number green conditional formatting makes all the numbers that are over 0 green in the column and i don't want that.

• ✭✭✭✭✭✭

can you attach a picture of your sheet with any sensitive information blocked out so I can better understand?

• ✭✭✭✭

I need formatting for the "difference" rows only not all the numbers so if the difference is a positive number the number is green, if its a negative number it shows red. conditional formatting applies to the column not rows and i don't want the numbers in the non "difference" rows in the column to be green because they're positive numbers.

• ✭✭✭✭

BTW this is different than my original i created an extra row to calculate the difference. originally i wanted the daily total to show red/green if higher/lower than the needed row.

• ✭✭✭✭

that did it.. i just couldn't figure it out.. thanks for your help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!