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

Jason Jordan

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?

L_123

In your Nurses column you have what appears to be unique values. Why don't you add them as criteria to the conditional formatting in order to pick your specific rows? or you can use an autonumber column and use that as an extra criteria.

L_123

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

https://help.smartsheet.com/learning-track/get-started/conditional-formatting

Jason Jordan

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.

L_123

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

Jason Jordan

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.

Jason Jordan

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.

L_123

In your Nurses column you have what appears to be unique values. Why don't you add them as criteria to the conditional formatting in order to pick your specific rows? or you can use an autonumber column and use that as an extra criteria.

Jason Jordan

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