excelexcel-formulaconditional-formatting

Conditional formatting highlight non matching criteria based on min date

I'm attempting to create a conditional format that highlights a retail that doesn't match the rest of the retails in a product ID based on the earliest (min) date's retail.

In the below example I have a retail of 24.99 on the earliest date of 8/24 and there is a retail of 20.99 (row 9) and I'd like to have that retail highlight.

I've been playing with the following formula =AND(SUMIFS(D:D,A:A,A2,E:E,MIN(E:E))<>COUNTIFS(E:E,MIN(E:E),D:D,D2)) but it's not quite right.

Example

Any help would be greatly appreciated! -D

Solution

  • You could try this, which says 'if there is a price with the minimum date for this product which is different from the current price, mark the current price as an error'

    =AND(COUNTIFS(A:A,A2,E:E,MINIFS(E:E,A:A,A2),D:D,"<>"&D2),E2<>"")

    There is a slight caveat, in that if you had a situation where there were two different prices for the minimum date in the same product, then all subsequent prices for this product would be highlighted.

    A filter would be an alternative:

    =LET(filRetail,FILTER(D:D,A:A=A2),filDate,FILTER(E:E,A:A=A2),AND(E2<>"",D2<>XLOOKUP(MIN(filDate),filDate,filRetail)))

    But I would deprecate this one - works but too slow:

    =AND(E2<>"",D2<>XLOOKUP(A2&"|"&MINIFS(E:E,A:A,A2),A:A&"|"&E:E,D:D))