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

#### mdhaumyu

##### New Member
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.

#### Attachments

• Largest and lowest value.jpg
35.4 KB · Views: 3

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### mabbutt

##### Board Regular
Have a look here.

#### mdhaumyu

##### New Member
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.

#### Attachments

• Largest and lowest value_2.jpg
33.5 KB · Views: 2

#### mabbutt

##### Board Regular
Try something like this:
Excel Formula:
``=H6=MIN(IF(H\$6:I\$19>0,H\$6:I\$19))``

#### mdhaumyu

##### New Member

Try something like this:
Excel Formula:
``=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

##### New Member
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.

#### Attachments

• Largest and lowest value_3.jpg
33.9 KB · Views: 2

#### mabbutt

##### Board Regular
Google search results found here.

#### mdhaumyu

##### New Member
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,

Replies
2
Views
148
Replies
3
Views
433
Replies
6
Views
154
Replies
13
Views
224
Replies
1
Views
254

1,148,396
Messages
5,746,452
Members
424,020
Latest member
LongDoo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back