MAXIFS in conditional formatting affecting values outside of set criteria?

Consider the following scenario: you have a dataset with car information, and you want to highlight the maximum value in column E ("Price") for cars of a specific type in column D ("Type"). Let's say you're interested in finding the most expensive "Hypercar" in the dataset.

You can use conditional formatting in Google Sheets to accomplish this. Here's a step-by-step guide:

1. Select the range of cells you want to apply conditional formatting to. In this example, let's select the range E2:E16, which contains the car prices.
2. Click the "Conditional formatting" icon on the toolbar or go to Format > Conditional formatting from the menu.
3. In the "Conditional formatting rules" sidebar, click the "New rule" button.
4. Select the "Custom formula" option from the drop-down menu.
5. In the "Formula" field, enter the following formula:

=(D2="Hypercar")*(E2=MAXIFS(E$2:E$16,D$2:D$16,"Hypercar"))

• This formula checks two conditions:
• (D2="Hypercar"): Checks if the car in row 2 is a "Hypercar."
• (E2=MAXIFS(E$2:E$16,D$2:D$16,"Hypercar")): Checks if the price in row 2 is the maximum price among all "Hypercars" in the range E$2:E$16.
• If both conditions are met, the cell will be highlighted.

Click the "Format cells if true" button to specify the formatting you want to apply to the cells that meet the condition. In this example, let's choose a bold font and a green fill color.

Click the "Done" button to apply the conditional formatting rule.

Now, the cells in the selected range that meet the specified conditions will be highlighted, allowing you to easily identify the most expensive "Hypercar" in the dataset.

Note: The formula provided in the other answer, =and(E2=max(filter(E$2:E,D$2:D="Hypercar")),D2="Hypercar"), will also work to achieve the same result.