Conditional Formatting Help

[Tennisguuy]

I have the following data in cells M4:Q13

Date	Amount	Status	Year	Loss Limit
1/8/2019	200,000		2018	100,000
10/15/2016	250,000		2016	150,000
12/12/2017	300,000		2017	100,000
8/1/2017	150,000		2017	150,000

I want to highlight the row where if the value in column P appear more than once I want but corresponding value in column Q has different value. I want to highlight the row with the lowest vale. For example 2017 appears twice but has two different value one for 100,000 and another for 150,000. I want highlight the one with 2017 for the year in column P and 100,000 in column Q since 100,000 is lower than 250,000.

The Date is in column M, Amount in column N, Status in column O, Year in column P and Loss Limit in column Q

I tried various formula but just can't get it to work the way I want

 

[mikerickson]

Try this CF formula

=AND(COUNTIFS(P:P,P2,Q:Q,">"&Q2)=(COUNTIFS(P:P,P2)-1),1<COUNTIF(P:P,P2))

Note that if there are two identical rows, neither will be highlighted.

 

[Eric W]

Select the range M2:Q5, click Conditional Formatting > New Rule > Use a formula, and enter:

=(COUNTIF($P$2:$P$5,$P2)>1)*($Q2=MIN(IF($P$2:$P$5=$P2,$Q$2:$Q$5)))

Select the fill color you want.

Sort of the opposite of Mike's, if there are 2 rows with the same minimum amount, they both will be highlighted.

 

[Tennisguuy]

I tried both and it is not working but I think it might be because the formula in not matching where the date is. The heading I have in rows M3:Q3. The data begins in row M4:Q4.

I changed Mike formula to AND(COUNTIFS(P:P,P4,Q:Q,">"&Q4)=(COUNTIFS(P:P,P2)-1),1<COUNTIF(P:P,P4)) and it kind of work but it only highlight the date in column M and I want the entire row to highlight.

I did the following before inputting the formula.

Selected cell M4:Q13>clicked conditional formatting>New Rule>Use Formula to determine which cell to format

 

[mikerickson]

To make it work for all those colums, change the references to absolute column, relative row (e.g. $A1) and then copy the CF from that one column, paste special validtation to the other columns.

 

[Eric W]

To adapt mine for that range:

=(COUNTIF($P$4:$P$13,$P4)>1)*($Q4=MIN(IF($P$4:$P$13=$P4,$Q$4:$Q$13)))

Note that the ranges in red have the $ sign before both the row and column, and the rows match your entire range, and the ranges in blue have the $ sign only before the column, and the row is the top row of your selection.

 

[Tennisguuy]

Thanks very very much appreciated. That worked