Find the Highest Value in a Category

Excel & Google Sheets

How it works

MAXIFS scans the amounts in column C but only considers rows where column A says Sales, then returns the largest of those. The range to maximize comes first, followed by pairs of criteria range and criteria — and you can add more pairs to narrow further, like MAXIFS(C:C,A:A,"Sales",B:B,"Ana Torres") for one person's best deal. Point the criteria at a cell instead of typing it — =MAXIFS(C:C,A:A,E2) — and the formula becomes a reusable lookup. It works in Google Sheets and Excel 2019 or newer; when nothing matches, it returns 0 rather than an error.

C:C

The numbers to find the maximum of — the Amount column.

A:A

The column to test — the Department column.

"Sales"

The category that rows must match. A cell reference like E2 works too.

When to use it

Use this for the biggest deal per department, the top score per team, or the most recent date per employee (dates are numbers, so MAXIFS finds the latest one). Pair it with a list of categories to build a one-line leaderboard.

Common mistakes

• ✕MAXIFS returns #NAME? in older Excel.

  ✓MAXIFS needs Excel 2019 or newer. In Excel 2016 and earlier, use the array formula =MAX(IF(A:A="Sales",C:C)) entered with Ctrl+Shift+Enter.

• ✕It returns 0 and you assume something's broken.

  ✓0 means no rows matched — often a spelling or trailing-space mismatch between your criteria and the category column. TRIM the category column and check the exact wording.

• ✕The ranges are different sizes.

  ✓MAXIFS errors if the max range and criteria range don't cover the same rows. Use whole columns (C:C and A:A) or identical row spans (C2:C100 and A2:A100).