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.
About David Ringstrom, CPA
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.