I am using MAXIFS in conditional formatting to color cells that contain the highest value in three different categories.As shown here
enter image description here
I am trying to format Column E to color gold the cells containing the highest value corresponding with the classes "Hypercar", "LMP2", and "GT3" shown in Column D.
I used the formulas =E2=MAXIFS(E$2:E$16,D$2:D$16,"Hypercar"),=E2=MAXIFS(E$2:E$16,D$2:D$16,"LMP2"), and =E2=MAXIFS(E$2:E$16,D$2:D$16,"GT3") in conditional formatting, and they colored the right cells, but they also colored other cells in Column E that contained the same number even when they did not correspond to the same class in Column D. In the image above you can see that cells E2, E4, E5, E7, and E8 have been colored gold. Only cells E2, E5, and E7 should be gold as E2 holds the highest value corresponding to the "hypercar" class, E5 holds the highest value corresponding to the "LMP2" class, and E7 holds the highest value corresponding to the "GT3" class. I presume cells E4 and E8 are colored as well because they contain the same value as E5 and E7 respectively. How can I prevent them from being colored? I only want cells colored that contain the highest value for each class. Thank you!
>Solution :
Added an extra condition check wrt. column_D to your existing formula:
=(D2="Hypercar")*(E2=MAXIFS(E$2:E$16,D$2:D$16,"Hypercar"))
