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:
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:
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:
Figure 3: Excel doesn't have a MAXIF function, but you can incorporate an IF statement with the MAX function.
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.