Creating Excel's Equivalent of MAXIF

Spreadsheets and graphs on a desk
Share this content

Periodically in Excel you may want to perform an analysis based on specific criteria. For instance, if I wanted to sum only the data in Figure 1 based on the month being equal to January, SUMIF has me covered. I could use COUNTIF to count the number of transactions in January, or in Excel 2007 and later use the AVERAGEIF function to average the sales of just January. However, what if I want to determine the largest amount just for the month of January? Although there's not a ready-made function, such as MAXIF, I can easily adapt the MAX function to accomplish this.

Figure 1: We will do an analysis of this data based on specific criteria.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.


Please login or register to join the discussion.

By Cartwright
Jun 26th 2015 01:11


Thanks (0)
By bill
Jun 26th 2015 01:11

Using your worksheet data in cols A:B
in D1 enter "=+A1"
in D3 enter "=DMAX(A:B,"amount",D1:D2)"
in D2 enter the name of the month for which you want to find the maximum value, or simply "=" and then click on the desired month anywhere in the period column, then the Enter key

Thanks (0)