# 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: 1

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### 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: 1

#### mdhaumyu

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

Regards,

Replies
6
Views
55
Replies
1
Views
85
Replies
6
Views
206
Replies
25
Views
1K
Replies
2
Views
146

1,133,270
Messages
5,657,749
Members
418,411
Latest member
Excellency

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

### Which adblocker are you using?

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

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