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.
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))
As of your formula, this one checks Product ID and Retail based on the earliest Date
=IF((XLOOKUP(MIN($E$2:$E$10),$E$2:$E$10,$D$2:$D$10)<>$D2)*($A2=XLOOKUP(MIN($E$2:$E$10),$E$2:$E$10,$A$2:$A$10)),TRUE,FALSE)
The Apply to is D2:D10.
The first XLOOKUP checks the price(<>), and the second the ID (=).
Multiplication is an AND logical operation on the two results.
EDIT: (on comment)
=LET(red,FILTER(D:D,A:A=A2),
tim,FILTER(E:E,A:A=A2),
IF(XLOOKUP(MIN(tim),tim,red)<>D2,TRUE,FALSE))
This formula will check in groups of ID-s for the same criterias. With whole columns, on the sheet there cannot be any irrelevant data in them for proper results.
