Creating Excel's Equivalent of MAXIF

exceltips.jpg

Spreadsheets and graphs on a desk
xfgiro/istock
Share this content
2

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.

Replies

Please login or register to join the discussion.

Cartwright!

Thanks (0)

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)