Highlight the largest value and lowest value of the range obtained from countif values

[mdhaumyu]

Requirement: To highlight the largest value and lowest value of the range.

The values in the range are obtained from countif() formula (for e.g. for March 4th, formula is COUNTIF('A:A,"04.03.2021"))

What I did: I am using conditional formatting rules: Top 1 and Bottom 1 to highlight the largest value and lowest value of the range.

Result: It is perfectly working for the largest value, but since there are zero values (obtained from countif), all cells having zero values are highlighted as lowest value.

I want to ignore the zero value and highlight the lowest value.

Any idea on how to do this?

Thank you.

 

[mabbutt]

Have a look here.

 

[mdhaumyu]

Have a look here.

Thank you for your help. It is working well only one set of range (refer the attached). Due to some constraints, I am having the range divided into 2 sets (P75:P89 and S75:S90).

How to apply this formula for combined range? I tried few formulas but failed.

 

[mabbutt]

Try something like this:

=H6=MIN(IF(H$6:I$19>0,H$6:I$19))

 

[mdhaumyu]

Try something like this:

=H6=MIN(IF(H$6:I$19>0,H$6:I$19))

Thanks a lot.

Hmm...such a simple solution, and I kept on trying complex formulas

 

[mdhaumyu]

Thanks a lot.

Hmm...such a simple solution, and I kept on trying complex formulas

Why is the MAX formula not working, I am using below forumalas:
=P75=MIN(IF($P$75:$S$90>0,$P$75:$S$90))
=P75=MAX(IF($P$75:$S$90>0,$P$75:$S$90))

MIN is working well, but MAX isn't.

 

[mabbutt]

Google search results found here.

 

[mdhaumyu]

Google search results found here.

Thanks a lot, I am able to get the maximum value highlighted (by removing >0 in the above formula).

Regards,