Creating Excel's Equivalent of MAXIF

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.

 

Let's first take a look at a couple of the functions that I mentioned. Users unaware of SUMIF might add up the values for January based on simple arithmetic, such as a formula like =B2+B6+B10. This formula is easy enough to write for small data sets, but quickly becomes unwieldy as the data sets grow. A better alternative is the SUMIF function, which has three arguments:

  • Range. I often explain this as where to look. In this case we'll specify cells A2:A13.
  • Criteria. I usually explain this as what to look for, which in this case could be "January" (be sure to include the double-quotes). A better approach is to reference a cell that contains the word January, such as cell D1.
  • Sum_Range. Think of this as what to add up. In this case we'll specify cells B2:B13.

After we type January in cell D1, we can then enter this formula in cell E1:

=SUMIF(A$2:A$13,D1,B$2:B$13)

You'll notice that I placed dollar signs before some of the row numbers. I did so in case I decide to enter February in cell D2, March in D3, and April in D4. I can then click on cell E1 and then double-click the Fill Handle, as shown in Figure 2, to copy the formula down the additional rows. If I left out the dollar signs, then in cell E2 the formula would become =SUMIF(A3:A14,D2,B3:B14). Now I did want D1 to change to D2, but I didn't want A2:A13 or B2:B13 to change to reference rows 3 through 14. Doing so means I could end up leaving some data out of my analysis.

Figure 2: The SUMIF function can sum data based on specific criteria, which in this case is January.

 

Once you understand SUMIF, you can easily use COUNTIF as well. Since there's nothing to sum, COUNTIF only has a Range and Criteria argument, which will return the number of times that January appears in cells A2:A13.

AVERAGEIF uses the same arguments as SUMIF, but will return the average of the amounts for January. Let's say, though, that you want the highest amount only for the month of January. Excel doesn't have a MAXIF function, but you can write a special type of formula known as an array formula that will do the trick instead.

First, let's look at the MAX function, which returns the largest value from a range of cells or a series of numbers separated by commas. Based on the data in Figure 1, the formula =MAX(B2:B13) would return 462, which is the largest number in that range of numbers. To get the largest values for the month of January we'll have to incorporate an IF statement into our formula, as illustrated in Figure 3:

=MAX(IF(A2:A13=D1,B2:B13))

Once you type this formula be sure to type Ctrl-Shift-Enter so that Excel knows this is an array formula. If you simply press Enter, Excel will return zero as the result. Array formulas allow you to perform sophisticated calculations that wouldn't otherwise be possible within a single worksheet cell.

Once you have the concept down, it's easy to use this technique with other functions, such as finding the smallest value for the month of January. Excel doesn't have a MINIF function, but you can transform the MIN function in the same way we did the MAX function:

=MIN(IF(A2:A13=D1,B2:B13))

Figure 3: Excel doesn't have a MAXIF function, but you can incorporate an IF statement with the MAX function.

 

About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.

 

You may like these other stories...

You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...
For bitcoin users, the taxman cometh. And you best know how to calculate taxes owed on what the IRS calls convertible virtual currency.In March 2014, the IRS issued Notice 2014-21, which declares virtual currency will be...

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.
Oct 30
Many Excel users have a love-hate relationship with workbook links. For the uninitiated, workbook links allow you to connect one Microsoft Excel spreadsheet to other spreadsheets, Word documents, databases, and even web pages.