Spreadsheets

MAXIFS and MINIFS: highest expense per category in Excel

Use MAXIFS and MINIFS to find the highest and lowest expense in each category, then look up the date it happened. Covers Excel 2019 and later plus Google Sheets.

Editorial illustration for MAXIFS and MINIFS: highest expense per category in Excel

MAXIFS returns the largest value that meets your conditions and MINIFS returns the smallest, so =MAXIFS(amounts, categories, "Food") gives your biggest food expense. Add an INDEX and MATCH lookup and you can see which date it was. Both functions work in Excel 2019, Excel 2021, Microsoft 365 and Google Sheets, but not in standalone Excel 2016 and older.

What is the syntax?

The first argument is the range you want the maximum or minimum from. After it come pairs of criteria range and criterion. Assume a sheet called Log with Date in column A, Category in column B and Amount in column C, rows 2 to 500. On a summary sheet, list categories in F2 downward, then:

  • G2 (highest): =MAXIFS(Log!$C$2:$C$500, Log!$B$2:$B$500, $F2)
  • H2 (lowest): =MINIFS(Log!$C$2:$C$500, Log!$B$2:$B$500, $F2)

Fill both down for every category. To limit it to one month, add two more pairs, for example Log!$A$2:$A$500, ">="&$B$1 and Log!$A$2:$A$500, "<"&EDATE($B$1,1), where B1 holds the first day of the month.

How do you find the date of the biggest expense?

The maximum tells you how much, but not when. You need the row where both the category and the amount match. In I2 use:

=INDEX(Log!$A$2:$A$500, MATCH(1, INDEX((Log!$B$2:$B$500=$F2)*(Log!$C$2:$C$500=$G2), 0), 0))

The inner part multiplies two true or false tests, so only rows that satisfy both become 1. MATCH finds the first 1 and INDEX returns that row's date. Wrapping it in INDEX(..., 0) makes it work without special array entry. Format column I as a date. In Excel 365 you could use =XLOOKUP(1, (Log!$B$2:$B$500=$F2)*(Log!$C$2:$C$500=$G2), Log!$A$2:$A$500) instead.

In Google Sheets, use =INDEX(Log!$A$2:$A$500, MATCH(1, ARRAYFORMULA((Log!$B$2:$B$500=$F2)*(Log!$C$2:$C$500=$G2)), 0)).

What does the summary look like?

With invented numbers for one quarter:

CategoryHighestDate of highestLowest
Groceries₹4,85014-Oct₹95
Fuel₹3,10002-Sep₹500
Dining out₹2,64026-Nov₹120
Medical₹7,20018-Aug₹60

A range between ₹60 and ₹7,200 tells you medical spending is lumpy. The groceries range, ₹95 to ₹4,850, suggests that the larger number is a monthly shop and the smaller ones are top-ups, which is a useful distinction when you set a budget.

Why does MINIFS sometimes return zero or a negative?

  • If no row matches, both functions return 0, not an error. A category with no entries will show 0, so read it alongside a COUNTIFS check.
  • If refunds are logged as negative amounts, MINIFS will show the refund as the lowest value. Keep refunds in a separate column or type.
  • Amounts stored as text are ignored. If a total looks too small, check that the cells are real numbers.

To avoid the first problem, wrap the result: =IF(COUNTIFS(Log!$B$2:$B$500, $F2)=0, "", MINIFS(Log!$C$2:$C$500, Log!$B$2:$B$500, $F2)).

What if you have Excel 2016 or older?

Those versions do not have MAXIFS or MINIFS, and typing them gives a #NAME? error. The older route is AGGREGATE: =AGGREGATE(14, 6, Log!$C$2:$C$500/(Log!$B$2:$B$500=$F2), 1) gives the maximum and function 15 with the same arguments gives the minimum. It ignores the rows that divide by zero.

Add a column for the average beside the highest and lowest, using =AVERAGEIFS(Log!$C$2:$C$500, Log!$B$2:$B$500, $F2). A category whose highest value is many times its average has a few big items hiding behind a calm-looking total, and those are the ones to understand before you set next month's budget.

What do you do with these numbers?

Use the highest value as a sanity check on your budget. If the highest grocery payment is ₹4,850 and your weekly grocery budget is ₹2,000, you know the budget is being broken by the big shop, not by daily purchases. And a category whose highest and lowest values are close together is probably predictable enough for a fixed monthly allowance.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports